SARGable expressions and performance

Understanding the concept of SARGable expressions can dramatically help you speed up query performance. Here’s how:

What is SARGable?

SARG is an acronym of sorts for “search argument”. In plain english, a SARGable query is where you can use an index seek, because you’re looking for a single value or range of values in the index. The opposite of a SARGable query is when you perform some type of function or calculation on each row in the table, and match the result of this calculation to your search criteria. When you can’t use an index to look for a single value or range, you’re performing a table scan or index scan, which means you’re traversing the entire table or index, which is a costly operation if there are a lot of rows.

Of course, the entire performance benefit from SARGable expressions comes from having an index that covers the columns you’re querying in the first place. Without such an index, you will automatically end up with a table scan or index scan anyway.

Some test data

Here’s a table with some test data, used in the rest of the article:

CREATE TABLE #testData (
    [ID]        int IDENTITY(1, 1) NOT NULL,
    [date]        datetime NOT NULL,
    PRIMARY KEY CLUSTERED ([ID])
);

CREATE UNIQUE INDEX #IX_testData ON #testData ([date]);

DECLARE @date datetime={d '2014-01-01'};
WHILE (@date<{d '2014-01-02'}) BEGIN;
    INSERT INTO #testData ([date]) VALUES (@date);
    SET @date=DATEADD(ss, 30, @date);
END;

INSERT INTO #testData ([date]) SELECT DATEADD(dd,  1, [date]) FROM #testData;
INSERT INTO #testData ([date]) SELECT DATEADD(dd,  2, [date]) FROM #testData;
INSERT INTO #testData ([date]) SELECT DATEADD(dd,  4, [date]) FROM #testData;
INSERT INTO #testData ([date]) SELECT DATEADD(dd,  8, [date]) FROM #testData;
INSERT INTO #testData ([date]) SELECT DATEADD(dd, 16, [date]) FROM #testData;

Scalar calculations

Here’s an example using a scalar function, the DATEPART() function.

In the first example, we’re looking for records in a table dated january 15th. In the top query, we’re using DATEPART() to filter the correct year, month and day. These function calls are called once for every row in the table, which means that we’re performing a costly index scan.

In the lower of the two queries, we’re looking for a range of dates instead, which allows us to benefit from the index on the date column.

--- Not SARGable expression: Index scan
SELECT [date]
FROM #testData
WHERE DATEPART(yy, [date])=2014 AND
      DATEPART(mm, [date])=1 AND
      DATEPART(dd, [date])=15;

--- SARGable expression: Index seek
SELECT [date]
FROM #testData
WHERE [date]>={d '2014-01-15'} AND
    [date]<{d '2014-01-16'};

You’ll notice the performance difference when you look at the query plan for these two queries:

Arithmetic operators

The same logic applies when you’re using arithmetic operations. Note the difference in the following examples between adding 1000 to the ID column, as opposed to subtracting 1000 from the search criteria.

--- Not SARGable expression: Index scan
SELECT [ID], [date]
FROM #testData
WHERE [ID]+1000>=41321 AND [ID]+1000<44201;

--- SARGable expression: Index seek
SELECT [ID], [date]
FROM #testData
WHERE [ID]>=41321-1000 AND [ID]<44201-1000;

Wildcard comparisons

Obviously, the same is true for wildcards and string operations.

--- Not SARGable expression: Index scan
SELECT FirstName, LastName
FROM Person.Person
WHERE LastName+'' LIKE 'Hill%';

--- SARGable expression: Index seek
SELECT FirstName, LastName
FROM Person.Person
WHERE LastName LIKE 'Hill%';

Except, here, you wouldn’t expect the second query to be SARGable, would you? The fact is, however, it is. The reason is that placing the wildcard operator at the end of the string enables the server to look for a range of values. Here’s how the query is actually interpreted by the server (look at the bottom of the tooltip in the query plan):

SELECT FirstName, LastName
FROM Person.Person
WHERE LastName>='Hill' AND LastName<'HilM' AND LastName LIKE 'Hill%';

So, because the wildcard in this expression is at the end of the string, the server can evaluate a range or records (from Hill to HilM) and then apply the wildcard to this range, which is still a lot more effective than performing an index scan.

How to eliminate non-SARGable queries

A lot of functions and scalar operations have a corresponding inverse function or operation. You can make a query SARGable if you can replace the use of these scalar functions (in the WHERE or JOIN clauses) with their inverse functions.

Example:

--- Replace this:
WHERE salesAmount/1000>50.00
--- ... with this:
WHERE salesAmount>50000.00

Until next week!