Re: What is the best way to do this?

From: Stephane Faroult <>
Date: Sun, 03 Apr 2011 17:32:05 +0200
Message-ID: <>


     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.


    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.

If I were you I'd renegotiate the scope.

Stephane Faroult
RoughSea Ltd <>
Konagora <>
RoughSea Channel on Youtube <>

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

Received on Sun Apr 03 2011 - 10:32:05 CDT

Original text of this message