I recall a time I was working on MSSQL and wrote a killer update statement that
took 35 minutes to run. I was trying to figure out how to improve the time so I
asked someone with more experience then me to look at it. He looked at it for a
while and said that he couldn't figure out how the query actually worked, but if
it were him he would write it in 5 statements.
I laughed at him (thinking I was much better), but as an experiment I rewrote it
in 5 simple statements and the whole thing ran in under a minute.
As I understand it, the planner does what you ask it to. If you tell it to do 4
inner joins and 7 left joins along with a couple sub-selects while munging the
data using functions, it will.
OTOH throwing data into a temporary table and running a couple of updates on it
and then updating 1 table against the second (or selecting the new data, for
that matter) can be a lot less work.
Sim
Ivan Sergio Borgonovo wrote:
> On Sun, 20 Jan 2008 09:30:11 +0200
> Sim Zacks <sim@compulab.co.il> wrote:
>
>> I've found that multiple simple statements often work faster then
>> convoluted single statements.
>
> I don't want to steal any further time to anyone considering my
> interest now is just academic since I can't handle any more
> information I could put into action shortly... but still is there a
> good reason that several simple statement could run faster than a
> more complex one?
>
> Is the planner able to correlate nearby simple statements?
> I'd think that a more complex statement gives more clues on what's
> your target to the planner that can then find a better way to
> achieve it.
> And yeah... on more complex statement the human can make things
> worse, writing unnecessary complex sql.
>
>
