Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL Short Circuiting Expand / Collapse
Author
Message
Posted Wednesday, April 27, 2005 6:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 06, 2006 4:52 AM
Points: 1, Visits: 1
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.
Post #178134
Posted Wednesday, April 27, 2005 7:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 11:06 AM
Points: 164, Visits: 361
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://sqlblog.com/blogs/rob_farley
Post #178143
Posted Thursday, March 03, 2011 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 06, 2011 12:49 PM
Points: 6, Visits: 20
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/. (It'll probably work for the example shown above, but be aware that this depends on undocumented behavior that is subject to change.)
Post #1072848
Posted Monday, March 21, 2011 7:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 06, 2011 12:49 PM
Points: 6, Visits: 20
Update: that exception to normal T-SQL CASE short circuiting should be fixed in an upcoming SQL release, per the Connect bug. It's still the behavior in SQL2005-SQL2008R2 (and maybe SQL2000), so keep an eye out for it as you use CASE for short-circuiting in existing releases.
Post #1081704
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse