November 13, 2007 at 3:22 am
Does anyone know if there is any difference between the following statements? Now there are big amounts of data so it could be the way the query is formed but in terms of syntax, will there be any performance gains?
IF EXISTS(....)
AND EXISTS(....)
AND EXISTS(....)
OVER
IF EXISTS(....)
IF EXISTS(....)
IF EXISTS(....)
I'd imagine they will be handled the same but one of the developers has said that it will give some performance gains? What do you think?
November 13, 2007 at 5:03 am
It will depend on your statement passing the specified criteria.
The first option (with the AND's) will resulve all of the EXISTS criteria every time.
The second option (nested IF's) will only resolve the EXISTS statements until it finds one that fails.
So, if you have 3 exists clauses and the first one fails, your first statement will run all three and the second will stop after the first one fails. This wiill make the second option (nested IF's) faster.
If, however, your criteria all pass, the first statement (with all of the AND's) will be slightly faster (a difference probably having to be measured in fractions of miliseconds) because it will not have the overhead of creating execution plans for each subsequent conditional.
It will take very little time to write this both ways and look at the query time on the execution plans - I recommend you do this and see for yourself.
November 13, 2007 at 6:31 am
Thanks for the reply. I was just in the middle of checking this against the execution plans... Cheers for the prompt feedback.
M
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply