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

How to Short-circuit Where Clauses Expand / Collapse
Author
Message
Posted Saturday, September 22, 2007 12:41 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2008 4:05 AM
Points: 291, Visits: 32
Comments posted to this topic are about the item How to Short-circuit Where Clauses


Paul Ibison
Paul.Ibison@replicationanswers.com
Post #401444
Posted Monday, November 26, 2007 12:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 15, 2013 11:54 AM
Points: 4, Visits: 12
any reason you can't just write:

select * FROM TblTest
where
SupplierId = @Input
OR
@Input = -1

Post #425960
Posted Thursday, December 27, 2007 8:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 27, 2007 1:49 PM
Points: 2, Visits: 7
Both methods (Using an OR, and Using the CASE block) FAIL to "Short Circuit".

In this simple example it doesn't matter, but in this nightmare query I've recently inherited, I've got something like this:

SELECT STUFF
FROM a bunch of Tables
WHERE (@InputA IS NULL
OR
EXISTS (SELECT 1 FROM a bunch of Tables WHERE SomCol = @InputA)
)
AND
(@InputB IS NULL
OR
EXISTS (SELECT 1 FROM a bunch of Tables WHERE SomCol = @InputB)
)
AND ETC...

SQL is resolving each subquery even when the input parameters are NULL (even when I re-wrote the query to use the CASE blocks in the WHERE Clause as this article suggested).

I re-wrote the query using Dynamic SQL (building the where clause in a string), and that worked, but it's gonna be a nightmare to maintain and modify over time.

Post #436694
Posted Thursday, December 27, 2007 11:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
Just note that this method will almost certainly prevent index seeks from been used to find affected records. It'll work, but it ain't likely to work fast.

It won't be noticeable on 6 rows. 600 000 is a very different story.

My usual suggestion for problems like this is either separate queries (if the number of parameters is low) or dynamic SQL (if the number of params is large)
Most of the other fancy tricks perform terribly on larger row sets.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #436773
Posted Thursday, December 27, 2007 12:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 27, 2007 1:49 PM
Points: 2, Visits: 7
Yep - sounds like dynamic SQL is my best bet, I've got lots of optional parameters and lots of rows.

Maybe in the future, Microsoft will improve their optimization algorithms for this type of situation...

Thanks for the advice!
Post #436808
Posted Thursday, December 27, 2007 12:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
Any form of function on a column in the where clause prevents index seeks, even something like UPPER, LEFT or the like.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #436818
Posted Thursday, December 27, 2007 1:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
It's a clever solution to complex lookups in small tables (very few rows), where indexes won't matter. On larger tables, it's going to bypass indexes and quite probably slow way down.

One solution I've used on moderately complex where statements is using Union All instead of Or. Makes it a bit more complex to maintain, but speeds things up quite nicely (assuming the tables have appropriate indexes on them).

select col1, col2, col3
from dbo.table1
where col1 = @param1_in and @param2_in is null
union all
select col1, col2, col3
from dbo.table1
where col2 = @param2_in and @param1_in is null

instead of:

select col1, col2, col3
from dbo.table1
where col1 = isnull(@param1_in, col1)
or col2 = isnull(@param2_in, col2)

or:

select col1, col2, col3
from dbo.table1
where col1 = @param1_in and @param1_in is not null
or col2 = @param2_in and @param2_in is not null

On a big table with decent indexes, the first solution is much faster, because it uses the indexes, than either of the later two solutions, or the solution proposed in the article.

(If the parameters are not mutually exclusive, then use "Union" instead of "Union All", or you'll get duplicate rows. Unless this is part of a sub-query or other situation where duplicate rows don't matter. Union is slower than Union All, but it's still generally faster than skipping indexes by using Or in your Where clause.)

With multi-table selects, complex Where clauses and a dozen or so parameters with complex relations, the Union solution becomes much too complex to build and maintain, and Dynamic SQL is better.


- 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
Post #436825
Posted Friday, January 2, 2009 9:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
bbbrian007 (12/27/2007)
Yep - sounds like dynamic SQL is my best bet, I've got lots of optional parameters and lots of rows.

Maybe in the future, Microsoft will improve their optimization algorithms for this type of situation...



Bear in mind that dynamic SQL is frowned upon for 2 reasons ;
i) possibility of SQL Injection attacks
ii) Optimiser unable to cache query plans (as the SQL is prepared dynamically)


Post #628862
Posted Friday, January 2, 2009 11:23 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 4,358, Visits: 9,537
Preet (1/2/2009)
bbbrian007 (12/27/2007)
Yep - sounds like dynamic SQL is my best bet, I've got lots of optional parameters and lots of rows.

Maybe in the future, Microsoft will improve their optimization algorithms for this type of situation...



Bear in mind that dynamic SQL is frowned upon for 2 reasons ;
i) possibility of SQL Injection attacks
ii) Optimiser unable to cache query plans (as the SQL is prepared dynamically)




The first one can be avoided by using 'sp_executesql' and validating/passing parameters as needed.

The second one is no longer valid - as long as the dynamically created SQL is the same. And that includes spaces, formatting, etc... The query plans will be cached and available for re-use.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #628999
Posted Friday, January 2, 2009 3:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
Good golly... I'm glad I'm not the only one that believes in the usage of well formed and safe dynamic SQL. :)

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #629158
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse