Use the correct filtering parameters to improve query performance

In today's article I would like to talk about the special performance issues associated with indexes in SQL Server.

Problem Description

Assuming the following simple query, in your daily SQL Server, such a query you have seen hundreds of times:

 1  --  2  select  Span style= "color: #808080;" >*  from   Sales.SalesOrderHeader  Span style= "color: #008080;" >3  where  year  (OrderDate) =  2005  and  Span style= "color: #ff00ff;" >month  (OrderDate) =  7  4  go  

With that simple query, we request sales information for a specific month in a particular year. is not complicated. Unfortunately, this query is poorly performing-even if a nonclustered index is used in the OrderDate column. When you view the execution plan, you see that the query optimizer has selected a nonclustered index on the OrderDate column, but unfortunately the full scan of the index for SQL Server is not an efficient find operation.

This is really not the limitation of SQL Server, but the way the relational database works and thinks: As long as you use an expression (function call, calculation) on the index column (that is, the so-called filter parameter (Search Argument)), the database engine must scan the index, not the lookup operation.

Workaround

in order to get an extensible lookup operation in the execution plan, you have to rewrite your query in a different way to avoid the invocation of the DATEPART function:

 1  --  2  select  Span style= "color: #808080;" >*  from   Sales.SalesOrderHeader  Span style= "color: #008080;" >3  where  OrderDate >=   '  

from the rewritten query you can see that the query returns the same results, but we have removed the DATEPART The invocation of the function. When you look at the execution plan, you see SQL Server doing a lookup-in that case, this is the so-called : SQL Server finds the 1th value and then scans to the maximum value of the request scope. If you want to invoke the function in the index column context, you must ensure that in the query, the execution of these function calls is on the right side of your column. Let's look at a concrete example. The following query converts the Creditcardid index column to char (4) data type:

 1  --  2  select  Span style= "color: #808080;" >*  from   Sales.SalesOrderHeader  Span style= "color: #008080;" >3  where  cast  (Creditcardid as  char  (4 ) ) =   " 1347   " 4   

1 -- Results in an Index Seek 2 SELECT *  from Sales.SalesOrderHeader 3 WHERE = CAST ('1347'asINT) 4 GO

Summary

As you can see from this article, it is important that you do not call any function directly in your index column or call the function indirectly. Otherwise, SQL Server will scan your index instead of performing an efficient find operation. And when your watch grows larger, the scan never expands.

If you encounter other good examples of this particular behavior and want to share it, please leave a message.

Thanks for the attention.

Use the correct filtering parameters to improve query performance