Sargable queries

A T-SQL query is said to be sargable (Search ARGument ABLE), if the query is written in such a way that is can take advantage of an index should a suitable index exist.

An example is two different ways to select the Adventure Works Sales Orders for SalesOrderID less than 45,000: –


USE AdventureWorks2012;

SET STATISTICS IO ON;

SELECT SalesOrderID,
RevisionNumber,
OrderDate,
DueDate
ShipDate
FROM Sales.SalesOrderHeader
WHERE SalesOrderID - 45000 < 0;

SELECT SalesOrderID,
RevisionNumber,
OrderDate,
DueDate
ShipDate
FROM Sales.SalesOrderHeader
WHERE SalesOrderID < 45000;

If we look at the actual query plan, we see: –

Sargable query plan

And the logical reads are: –

(1341 row(s) affected)
Table ‘SalesOrderHeader’. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1341 row(s) affected)
Table ‘SalesOrderHeader’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

So the second example has 5% of the logical reads of the first to return the same result-set.

A real-world example where I was able to achieve massive performance gains was in an SSRS report. A text SSRS parameter was being used in the T-SQL select statement as a where clause predicate. SSRS made this variable the NVARCHAR data type whereas the database column filtered was VARCHAR. This resulted in a table scan and hundreds of thousands of logical reads. I changed the T-SQL select statement to a stored procedure and made the query sargable by removing the data type implicit conversion. An index seek rather than scan resulted meaning the end-users received their report far quicker, less server resources were used and shared locks were held for a fraction of the time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s