Conor vs. more SARGable predicates

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