December 10, 2009 at 11:57 am
Hi,
I have the following piece of sql in a Stored procedure
SELECT * FROM Table1
WHERE
(@State = 1 AND Table1.ID IN (SELECT ID FROM Table2 WHERE..)) OR
(@State = 2 AND Table1.ID IN (SELECT ID FROM Table2 WHERE..)) OR
(@State = 3 AND Table1.ID IN (SELECT ID FROM Table2 WHERE..))
The Where statements of Table2 vary depending on the @State variable.
When i look at the execution plan i can see all 3 subqueries are evaluated.
But i was under the impression that when the part before the AND evaluates to False the part after it isn't evaluated.
Am i mistaken or do i have to set some SQL-setting?
Or should i rewrite the query in another way to get better performance?
Kind regards,
Marco
December 10, 2009 at 12:12 pm
It is my understanding, and I may be wrong, that SQL Server does not do short-curcuiting when evaluating the WHERE clause. If I am wrong, I'd welcome being corrected on this.
December 10, 2009 at 12:45 pm
I am under the same impression as Lynn, but what's more is that you can't guarantee which part of the AND will be evaluated first.
December 10, 2009 at 12:56 pm
It can do some short-circuiting, but it has to include all possibilities in the execution plan.
Sometimes, you can improve performance and create a simpler execution plan by using Unions instead of Or statements, but that's significantly less true in 2005/2008 than it was in 2000.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2009 at 1:01 pm
SQL doesn't consistently do position-based short circuiting. It can eliminate obvious contradictions, however there's another thing to consider. The execution plans will be cached for reuse. One rule that SQL has is that the execution plan MUST be safe for reuse. That is, if the plan is reused it must produce correct results. That means that portions of the where clause cannot be eliminated if they involve parameters or variables, as those can change from one execution to another.
Take a read through this, you've got a variant of this pattern. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply