Comparing Conditional WHERE clauses

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Comparing Conditional WHERE clauses

Comparing Conditional WHERE clauses

Rate Topic

Display Mode

Topic Options

Author

Message

Erin Ramsay

Posted Monday, February 11, 2013 1:56 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 423, Visits: 782

I've been trying to find performance stats on different ways to do conditional WHERE clauses.

Take these two for example:

 WHERE (seq = @seq or @seq is null)

and

 WHERE seq = coalesce(@seq, seq)

I THINK I see a slight improvement with the coalesce conditional but I'd like someone else's opinion on it.

Thanks!

Post #1418626

Sean Lange

Posted Monday, February 11, 2013 2:20 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 8,632, Visits: 8,269

This looks a lot like a catch all type of query. Take a look at Gail's blog post about this. It explores a number of methods and comes up with a version that might surprise you as being the best performance wise.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1418633

Grant Fritchey

Posted Tuesday, February 12, 2013 4:08 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:36 AM
Points: 13,383, Visits: 25,184

Second vote for Gail's article.

That second query will only, ever, use a scan to retrieve data. Functions against columns in the WHERE or JOIN conditions always lead to scans.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1418861

ScottPletcher

Posted Tuesday, February 12, 2013 10:21 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

The first format is definitely preferable, although I prefer checking the variable first to give SQL as much chance as possible to short-circuit (bypass) the second comparison:

WHERE (@seq IS NULL OR seq = @seq)

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1419086

« Prev Topic | Next Topic »

Permissions