Re: What is the best way to do this?
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: Michael Moore <michaeljmoore@xxxxxxxxx>
- Date: Sun, 03 Apr 2011 17:32:05 +0200
Mike,
No need to apologize, I didn't spend hours on it. But the problem
is precisely the scope of what you are allowed to do, and that is a
problem that is very common.
<rant>
I have seen umpteen cases of queries (or processes) that, when seen
under a magnifier, are absolutely untunable - OK, let's say you can gain
10% after hours of testing of obscure parameters or features. But when
you see that the query that takes the most time on a machine is some
kind of "select attribute from mega_table where
sequence_generated_pk=:1" you know that something is seriously wrong
with the program and that no amount of "SQL tuning" can save you. Many
people on this list will tell you the same thing. I am flabbergasted by
the number of developers who before writing a join need to search the
web for "SQL tips", and the number of managers who seem to believe that
"SQL tuning" is something analogous to copy editing. It isn't.
Why not as blatant as the join-in-a-loop, your case is in my eyes very
similar and made worse by a totally twisted data repository (I cannot
decently call THAT a database). But if you walk up the stack of function
calls, perhaps one level up, perhaps two level ups, perhaps even more,
you probably have a big loop (you mentioned a batch) that returns
customer ids. This is where you need to hit, and hit hard, rethinking
what you are given as input and what you need to produce in the end.
This looks like a case where you can have performance gains of one order
of magnitude.
Needless to say, what you'd really need to have is a properly designed
database, because the SQL that is required to make any sense of what you
have to work with is probably slightly above the means of a developer
with a couple of years of experience.
</rant>
If I were you I'd renegotiate the scope.
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
On 04/03/2011 04:52 PM, Michael Moore wrote:
Response to Stephane's post which starts:
"Hmm, looks like one of the hidden reasons ..."
Stephane,
I feel a bit guilty that you made such a huge effort. While you
solution is very clever, it addresses the problem at a level that is
beyond the scope of what I am allowed to do. Specifically, I am not
trying to resolve the virtual column names in a given 'where clause'.
Instead, my task is to receive a single operand (virtual_column_name)
and resolve it and then return it's value which is stored in the ABC
table. I think my April 2nd post should make this clear.
My apologies for not being more clear from, the beginning.
Regards,
Mike
Other related posts:
