February 11, 2013 at 1:56 pm
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!
February 11, 2013 at 2:20 pm
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/[/url]
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 12, 2013 at 4:08 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 12, 2013 at 10:21 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply