Eat Sleep Data

02/19/2019Data Engineering

Improve SQL Performance with Sargable Expressions

What does Sargable mean?

Sargable, short for Search ARGument ABLE, is a definition in relational database management systems for queries that can take advantage of indexes to speed up the query. A query that fails this definition is known as a non-sargable query and usually has a negative effect on query performance. So, simply making a query sargable CAN increase its performance alone, but in concert with the right indexes the results can be dramatic.

A common non-sargable query is one that includes a function call in the WHERE clause. Below I’ll go through some concrete examples. However, sargability is not limited to just the where clause. It can effect the ORDER BY, GROUP BY and HAVING clauses.

The SELECT clause is the only one that can contain non-sargable expressions without negative effects on performance.

Please note that these are general rules and sargability can depend on the type of database you use.

Common Non-sargable examples

Including field in a function

SELECT ... 
FROM ...
WHERE Year(my_date) = 2008

The query optimizer can't use an index on my_date because it will have to evaluate the function year for every row causing a table scan. A sargable approach would be to use between or >= and <=.

WHERE my_date >= '01-01-2018' AND my_date <= '12-31-2018'

or

WHERE my_date BETWEEN '01-01-2018' AND '12-31-2018'

Other common examples:

Bad: WHERE isNull(FullName, 'Tom') = 'Tom'
Good: WHERE ((FullName = 'Tom') OR FullName IS NULL)

Bad: WHERE SUBSTRING(DealerName, 4) = 'Ford'
Good: WHERE DealerName LIKE 'Ford%'

Bad: WHERE DateDiff(mm, OrderDate, GetDate()) >= 30
Good: WHERE OrderDate < DateAdd(mm, -30, GetDate())

LIKE value beginning with a wildcard

WHERE Field LIKE '%blah%'

When using the LIKE operator, if you use a wildcard value at the beginning it causes a table scan.

Summary

Sargable operators: =, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL, EXISTS

Sargable operators that rarely improve performance: <>, IN, OR, NOT IN, NOT EXISTS, NOT LIKE

Recent posts