Improve SQL Server database performance by using the correct search arguments

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

Improving Query performance by using correct Search Argumentsapril 8, 2014 • Klaus Aschenbrenner

Improve database performance by using the correct search arguments

Today's blog, I want to talk about a specific performance issue on SQL Server about indexing

Problem

Take a look at the following simple query statement, probably you've seen it hundreds of times before

--Results in a Index scanselect * from Sales.salesorderheaderwhere year (OrderDate) = 2005 and MONTH (OrderDate) = 7GO

Door code Query A sales information, need a specific month and year, this is not very complex. But unfortunately, the efficiency of this qeury is not good, even if OrderDate this column has been done non-clustered Index. You can look at the following Qeury execution diagram, you can see that query optimizer has selected non-clustered index defined under column OrderDate, but SQL Server does a full scan of index, Rather than expecting the seek operation.

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

Solution Solutions

In order to solve the problem of the door, we must avoid the list of direct functions, such as the above problems can be replaced by the following code

--Results in a Index seekselect * from Sales.salesorderheaderwhere OrderDate >= ' 20050701 ' and OrderDate < ' 200508 "GO"

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

--Results in a Index scanselect * from Sales.salesorderheaderwhere 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, this extensibility is pretty bad. If you put the function on the right side of the expression, SQL Server can execute the seek operation.

--Results in a Index seekselect * from Sales.salesorderheaderwhere creditcardid = CAST (' 1347 ' as INT) GO

Summarize

Through today's blog, I think you have realized that you should not apply the function directly on the indexed column, or SQL Server will scan your entire index instead of the seek operation. When your watch gets bigger, you'll crumble.

Translation PostScript

This is also when I look at the Microsoft SQL Server certification exam exam70-461 Trainingkit, it is repeatedly emphasized in the book. The simple thing is to ensure that you do not directly use the function on the index column , to use the function, the right side of the expression to work. As to why it affects performance. Because I am not familiar with index, I do not understand very clearly.

I guess the following is the first note, welcome to the discussion.

Do index to a certain column, is not similar to this column of data to make a hash map, when looking for this column of data, directly can do O (1) operation (is it speak of seek operation). If the function is used for this column, the SQL Server mechanism is not to redo a function of the column after the hash, it is a simple comparison of one. is an O (N) operation.

　　

　

　　

Improve SQL Server database performance by using the correct search arguments