Sargable
This article has multiple issues. Please help or discuss these issues on the talk page. (Learn how and when to remove these template messages)
|
In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.
A query failing to be sargable is known as a non-sargable query and typically has a negative effect on query time, so one of the steps in query optimization is to convert them to be sargable. The effect is similar to searching for a specific term in a book that has no index, beginning at page one each time, instead of jumping to a list of specific pages identified in an index.
The typical situation that will make a SQL query non-sargable is to include in the WHERE clause a function operating on a column value. The WHERE clause is not the only clause where sargability can matter; it can also have an effect on ORDER BY, GROUP BY and HAVING clauses. The SELECT clause, on the other hand, can contain non-sargable expressions without adversely affecting the performance.
- Sargable operators:
=, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL
- Sargable operators that rarely improve performance:
<>, IN, OR, NOT IN, NOT LIKE
Simple example[]
WHERE
clauses that are sargable typically have field values on the left of the operator, and scalar values or expressions on the right side of the operator.
Not sargable:
SELECT *
FROM myTable
WHERE SQRT(myIntField) > 11.7
This is not sargable because myIntField is embedded in a function. If any indexes were available on myIntField, they could not be used. In addition, SQRT()
would be called on every record in myTable.
Sargable version:
SELECT *
FROM myTable
WHERE myIntField > 11.7 * 11.7
This is sargable because myIntField is NOT contained in a function, making any available indexes on myIntField potentially usable. Furthermore, the expression is evaluated only once, rather than for each record in the table.
See also[]
References[]
- SQL Performance Tuning by Peter Gulutzan, Trudy Pelzer (Addison Wesley, 2002) ISBN 0-201-79169-2 (Chapter 2, Simple "Searches")
- Microsoft SQL Server 2012 Internals by Kalen Delaney, Connor Cunningham, Jonathan Kehayias, Benjamin Nevarez, Paul S. Randal (O'Reily, 2013) ISBN 978-0-7356-5856-1 (Chapter 11, The Query Optimizer)
External links[]
- Database management systems
- Relational model