Improve the performance of SQL Server databases by using the correct search arguments _mssql

Original address: http://www.sqlpassion.at/archive/2014/04/08/improving-query-performance-by-using-correct-search-arguments/

Today's article gives you a talk about a specific performance issue with indexing on SQL Server.

Problem

Look at the simple query statement below, you may have seen it hundreds of times

--Results in a Index Scan
SELECT * from Sales.SalesOrderHeader
WHERE year (OrderDate) = both in and MONTH (OrderDate) = 7 Go

The door-to-door code queries a sales message that requires a specific month and year, which is not very complicated. But unfortunately, this qeury is not efficient, even if OrderDate this column has done non-clustered Index. Take a look at the Qeury execution diagram below, and you can see that query optimizer has chosen to define non-clustered index under Column OrderDate, but SQL Server does a full scan of index. Rather than the desired seek operation.

This is not actually a SQL Server limitation, but rather relational database. As soon as you add a function to a column that does index (Search Argument), the database engine must scan the index again instead of directly executing the seek operation

Solution

In order to solve the problem of the door, we must avoid the direct function in the row, such as the above problem can be replaced by the following code

--Results in a Index seek
SELECT * sales.salesorderheader
WHERE OrderDate >= ' 20050701 ' and OrderDate &L T ' 20050801 ' Go

We rewrite this query statement to achieve the same effect without the function month. From the execution diagram of this query, SQL Server executes the seek operation, scan within the scope of the query. So, if you're using a function in a where query, use the right side of the expression to avoid performance problems. Like the following example.

--Results in a Index Scan
SELECT * from Sales.SalesOrderHeader
WHERE CAST (Creditcardid as CHAR (4)) = ' 1347 '
Go

This query causes SQL Server to scan the entire non-clustered Index. So when the table gets bigger, the extensibility is bad. If the function is placed on the right side of the expression, SQL Server can execute the seek operation

--Results in a Index seek
SELECT * from Sales.SalesOrderHeader
WHERE creditcardid = CAST (' 1347 ' as INT)
go

Summarize

By today's blog, I think you've learned not to apply a function directly on a indexed column, or SQL Server scans your entire index instead of the seek operation. When your watch becomes larger and bigger, you will collapse.

Translation PostScript

This is also when I look at Microsoft SQL Server certification exam exam70-461 Trainingkit, it is repeatedly emphasized in the book. In simple terms, it is guaranteed not to use functions directly on the column of the index, to use the function, to the right of the expression. As for why performance is affected. Because I am not familiar with the index, I understand is not very clear.

I guess the following, write down first, welcome discussion.

For a column to do index, is not similar to this column of data to do a hash map, when looking up this column of data, directly can do O (1) operation (whether it is the said seek operation). If you use a function on this column, the SQL Server's mechanism is not to redo a hash of the column that functions after the function, it is simply a comparison of one. Is the operation of O (N).