Conor vs. more SARGable predicates

  • Article

I got another question in the mail this week from another reader:

 

Hi Conor Thanks for an interesting blog. Maybe you have the answer to this question: Why I SQL Server having performance problems with these types of expressions: ------------------------------------------------------------------------------------------ DECLARE TABLE dbo.Archive( LastName varchar(50), Reference INT, SocialSecurityNumer VARCHAR(10) ) ----------------- DECLARE dbo.MySearch @Reference INT, @SSN VARCHAR(10) AS BEGIN SELECT * FROM dbo.Archive WHERE @SocialSecurityNumber IS NULL OR SocialSecurityNumber = @SSN AND @Reference IS NULL OR Reference = @Reference END ------------------------------------------------------------------------------------------ Or similarily WHERE SocialSecurityNumber = ISNULL(@SSN, @somethingelse) ------------------------------------------------------------------------------------------ So far I've been adapting to this reality. Erland Sommerskogs paper on this is great, and I've used all the tricks in book to make the performance acceptable. But why can it be so difficult to make get good execution plans with the above scenarios?

 

There are actually a lot of questions in here.  Let me try to tease out a few that I can answer.

1. Should I use table variables?

2. Why does OR block predicate sargability?

3. What’s up with ISNULL?

 

1. Should I use table variables?

 

Table variables are not fully supported by the optimizer’s reasoning model.  As such, they can cause performance problems when used in large queries with plan choices.  Generally, I tell customers to be very, very careful with them if they know what they are doing.  If they don’t know what they are doing, I would avoid them.  MSDN is doing some maintenance this morning, but Books Online has a Note that describes that these should not be used for anything “big” due to the lack of support here.  In the future, I am hopeful this situation can be improved.  Temp tables are the alternative to consider.

 

2. Why does OR block predicate sargability?

 

The logic that matches indexes needs to define a range.  OR (disjunctions) generally do not qualify as a single range.  There are a few things we can do in the optimizer, but overall you need to consider how to write your queries to get things to be SARGABLE.  Alternatively, you can add option (recompile) if this works for your application.  The optimizer will aggressively prune out OR branches for parameters (in recent releases) with that combination and this can make some predicates sargable with the query written with OR.

 

3. What’s up with ISNULL?

 

IsNull is a programming construct that is NOT the same as IS NULL.  IS NULL is fully understood by the optimize and is used in a number of spots.  ISNULL(), however, was really intended as a presentation concept that goes in the final SELECT list.  It should probably not be used in the WHERE clause because a number of functions, even built-in, are not supported in the logic that matches indexes.

 

Generally, I advise that you conceptually separate the “query” concepts from the “T-SQL” concepts (and most functions fall into the T-SQL bucket).  This will help you find areas where SQL Server may not generate an optimal plan.  Often, thinking about the problem in this manner will also help make sure that you isolate your business logic from your query logic a bit more cleanly as well.

 

I hope this helps you avoid problems when using the product in the future.

 

Thanks,

Conor

Comments

  • Anonymous
    December 18, 2010
    I just hope there were some parenthesis in the WHERE cause that didn't get to you, or wouldn't the results probably differ significantly from what I assume they wanted. i.e: SELECT * FROM dbo.Archive WHERE (@SocialSecurityNumber IS NULL OR SocialSecurityNumber = @SSN) AND (@Reference IS NULL OR Reference = @Reference)

  • Anonymous
    December 18, 2010
    You say ISNULL is understood by the optimizer, does it understand COALESCE as well?

  • Anonymous
    December 19, 2010
    Michael I understood Conor to say that ISNULL (the function) is not understood, so it would stand to reason that COALESCE also is NOT understood. Conor said IS NULL (the comparison operator) is what is understood.

  • Anonymous
    December 19, 2010
    aren't ISNULL and COALESCE handled differently internally? COALESCE gets expanded to a CASE statement, i believe.

  • Anonymous
    December 20, 2010
    The comment has been removed

  • Anonymous
    December 20, 2010
    expressions combined with ANDs can be independently applied to indexes.  col > 1 AND col2 < 20 - each of those can be index matches and then the results combined. OR expressions does not have this property when you start combining arbitrary expression trees (ORs over ANDs).  While there are some cases where OR conditions can be applied to an index, in general they do not match indexes as easily.  Algorithmically, it is not possible to do all cases in reasonable amounts of compilation time.  As most people structure their code to get better query plans, this is one of those areas where you can structure your code to get better query plan performance. Could SQL Server do more?  Sure, but it already does a fair amount in this space compared to naive implementations and ultimately you get better performance gains by writing your queries to get into the good algorithmic code paths.  Ultimately, customers have been demanding other features more than working on this specific part of the code (there are a lot of feature requests on our product). I appreciate your feedback and we'll obviously consider ways to improve our index selection code the next time that we add functionality to the index matching code.  

  • Anonymous
    December 25, 2010
    The comment has been removed