April 27, 2005 at 6:31 pm
I am trying to short circuit some code to help optimize a query and also to build a dynamic where clause. When I view The Query Execution in QA however the code that should not get executed gets executed anyway. Is there any way to force a short circuit?
My where clause consists of:
WHERE r.loc_isActive = 1 AND
r.hidden = 0 AND
( @adminSearch = 1 OR r.completedStep >= 4 ) AND
( @adminSearch = 1 OR r.searchable = 1 ) AND
( @adminSearch = 1 OR NOT EXISTS (SELECT * FROM [dbo].[UserLabels] WHERE userID = r.userID AND labelID = 5) ) AND
( @labelID = 0 OR EXISTS (SELECT * FROM [dbo].[UserLabels] WHERE userID = r.userID AND labelID = @labelID) )
In this example the parameter @labelID is indeed 0 but SQL Server still executes the EXISTS (SELECT * FROM [dbo].[UserLabels] WHERE userID = r.userID AND labelID = @labelID). It doesn't return the results from it so in that case the Short circuit works but the Execution Plan still shows it executing thus degrading the performance of the query when it is not necessary.
April 27, 2005 at 7:34 pm
Use a 'case' statement. That will stop the rest being executed. I've used '1=1' and '1=0' for the true and false scenarios. Check the logic of course... I didn't really pay much attention to it.
where r.loc_isactive = 1
and r.hidden = 0
and 1= case when @adminSearch = 1 then 1
when NOT r.completedStep >= 4 then 0
when NOT r.searchable = 1 then 0
when EXISTS (SELECT * FROM [dbo].[UserLabels] WHERE userID = r.userID AND labelID = 5) then 0
else 1 end
and 1= case when @labelID=0 then 1
when EXISTS (SELECT * FROM [dbo].[UserLabels] WHERE userID = r.userID AND labelID = @labelID) then 1
else 0 end
Rob
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
March 3, 2011 at 12:51 pm
Be ware that even CASE does not always provide deterministic order of evaluation with short circuiting. See http://bartduncansql.wordpress.com/2011/03/03/dont-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case/[/url]. (It'll probably work for the example shown above, but be aware that this depends on undocumented behavior that is subject to change.)
March 21, 2011 at 7:38 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply