Use the correct search arguments to improve the performance of the SQL Server database, argumentssql

Use the correct search arguments to improve the performance of the SQL Server database, argumentssql

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

Today's article will talk about a specific performance issue about indexing on SQL Server.

Problem

Look at the following simple query statement. You may have seen it hundreds of times.

-- Results in an Index ScanSELECT * FROM Sales.SalesOrderHeaderWHERE YEAR(OrderDate) = 2005 AND MONTH(OrderDate) = 7GO

The on-site code queries a sales information and requires a specific month and year, which is not very complicated. Unfortunately, this qeury is not efficient, even if the OrderDate column has been configured with a Non-Clustered Index. You can see that Query Optimizer has selected the Non-Clustered Index defined in the OrderDate column, but SQL Server has performed a complete Index scan, instead of the expected Seek operation.

This is not the limitation of SQL Server, but relational database. If you add a function operation to a Search Argument column, the database engine must scan the index again instead of directly executing seek operation.

Solution

In order to solve the door-to-door problem, you must avoid using functions directly in the column-to-door. For example, the above problem can be replaced by the following code.

-- Results in an Index SeekSELECT * FROM Sales.SalesOrderHeaderWHERE OrderDate >= '20050701' AND OrderDate < '20050801'GO

The query statement we have rewritten can achieve the same effect, without using the MONTH function. From the query execution diagram, SQL Server executes seek operation and performs scan within the query range. Therefore, if you want to use a function in the where query, use the right side of the expression to avoid performance problems. For example, the following example.

-- Results in an Index ScanSELECT * FROM Sales.SalesOrderHeaderWHERE CAST(CreditCardID AS CHAR(4)) = '1347'GO

This query will enable SQL Server to scan the entire Non-Clustered Index. Therefore, when the table becomes larger, the scalability and other aspects will be poor. If you place the function on the right of the expression, SQL Server can execute seek operation.

-- Results in an Index SeekSELECT * FROM Sales.SalesOrderHeaderWHERE CreditCardID = CAST('1347' AS INT)GO

Summary

Through today's blog, I think you have realized that you should not apply functions directly on indexed columns. Otherwise, SQL Server will scan your entire index instead of seek operation. When your table grows bigger, you will crash.

Postscript

This is also when I look at the TrainingKit of Microsoft SQL Server Certification Examination Exam70-461, it repeatedly stressed in the book. Simply put, do not directly use a function in the index column. To use a function, change it to the right of the expression. Why does it affect performance. Because I am not familiar with index, I do not understand it clearly.

I would like to guess the following. Write down the information first. Welcome to discuss it.

Index A column. Is it similar to performing a hash ing on the data of this column? when searching for the data of this column, O (1) can be directly performed) (is it about seek operation ). If a function is used for this column, the SQL Server mechanism will not re-create a hash of the column after the function, and it will be compared one by one. It is an O (N) operation.