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@xxxxxxxxxxxxxx> 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.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
