Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What makes a SQL statement sargable?

By definition (at least from what I've seen) sargable means that a query is capable of having the query engine optimize the execution plan that the query uses. I've tried looking up the answers, but there doesn't seem to be a lot on the subject matter. So the question is, what does or doesn't make an SQL query sargable? Any documentation would be greatly appreciated.

For reference: Sargable

like image 928
DForck42 Avatar asked Apr 28 '09 19:04

DForck42


People also ask

What is the difference between sargable and non sargable queries?

In other words, a sargable predicate is such that can be resolved by the storage engine (access method) by directly observing the table or index record. A non-sargable predicate, conversely, requires a higher level of the DBMS to take action.

What is Sarg SQL?

SARG is short for Search Argument. This is an important tuning term and something every developer and DBA should know.


2 Answers

The most common thing that will make a query non-sargable is to include a field inside a function in the where clause:

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

The SQL optimizer can't use an index on myDate, even if one exists. It will literally have to evaluate this function for every row of the table. Much better to use:

WHERE myDate >= '01-01-2008' AND myDate < '01-01-2009' 

Some other examples:

Bad: Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones' Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))  Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford' Fixed: Select ... WHERE DealerName Like 'Ford%'  Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30 Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate())  
like image 69
BradC Avatar answered Sep 30 '22 02:09

BradC


Don't do this:

WHERE Field LIKE '%blah%' 

That causes a table/index scan, because the LIKE value begins with a wildcard character.

Don't do this:

WHERE FUNCTION(Field) = 'BLAH' 

That causes a table/index scan.

The database server will have to evaluate FUNCTION() against every row in the table and then compare it to 'BLAH'.

If possible, do it in reverse:

WHERE Field = INVERSE_FUNCTION('BLAH') 

This will run INVERSE_FUNCTION() against the parameter once and will still allow use of the index.

like image 20
beach Avatar answered Sep 30 '22 04:09

beach