SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Short-circuit Where Clauses


How to Short-circuit Where Clauses

Author
Message
Paul Ibison
Paul Ibison
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1089 Visits: 32
Comments posted to this topic are about the item How to Short-circuit Where Clauses


Paul Ibison
Paul.Ibison@replicationanswers.com
FritzDotNet
FritzDotNet
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 12
any reason you can't just write:

select * FROM TblTest
where
SupplierId = @Input
OR
@Input = -1
bbbrian007
bbbrian007
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228375 Visits: 46342
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, MVP, M.Sc (Comp Sci)
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


bbbrian007
bbbrian007
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228375 Visits: 46342
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, MVP, M.Sc (Comp Sci)
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


GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58733 Visits: 9730
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
Preet_S
Preet_S
SSChasing Mays
SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)

Group: General Forum Members
Points: 600 Visits: 240
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)
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19527 Visits: 10042
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218297 Visits: 41995
Good golly... I'm glad I'm not the only one that believes in the usage of well formed and safe dynamic SQL. Smile

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search