Write high performing query: Is your query SARGABLE?

Hi Guys,

Today i wanna talk about performance that is the subject I like best!
.
I told you about this topic a few months ago Here. However, the article was written in Italian; I decided to translate it into English for the benefit of all.

I hope you like it!

Introduction: When a Query is a performing Query? 

First concept!

A Query is a performing Query when it takes advantage of the presence of the indexes present on the tables. Only in this way infact data will be extracted fast

Obviously we can create tables without indexes (called HEAP tables) but in this case the only way to extract data will be to read the table entirely.
This concept must be clear!

Let's see it through a simple example:

Create a PERSON table with these three columns: ID, FIRSTNAME and LASTNAME. Then we perform this simple query:

SELECT FIRSTNAME FROM PERSON WHERE FIRSTNAME = 'LUCA' 

 

If we look at the execution plan, we immediately see that the table is entirely scanned.
Well, let's take a step forward!

Now add an index on the FIRSTNAME columns through the command:

Create index IDX_PERSON_FIRSTNAME on PERSON (FIRSTNAME)

Now run the same Query and look at the execution plan:

What is the difference?
The difference is that:

• Now the index is used
• Only the affected lines are read

Simplifying a lot, we can extimate the gain of performance by considering roughly the execution time as directly proportional to the number of rows read: 18 rows vs. 600K rows!

So, our second concept:

Try to avoid that the Query we writing scan a table even if only trought an index (index scan)
Our queries should do an index seek!

Now a question:
Are we really sure that our Query will use an index and mostly will do a seek throu the index?

Not for sure!

Let's see why talking about the important concept of SARGABLE

Sargable Query

First, SARGABLE is an acronym given by the three words: Search ARGument ABLE

It is said that a query is SARGABLE if the DBMS engine can take advantage of an index to speed up the execution of the query.

Let's see ...

The simple Query written above is SARGABLE?
Yes, because it actually does the SEEK of the index I had just created.

Let's try now with a slightly more complicated query.

Let's retrieve, for example, all the people in which the first character of the name is "C" throu this query:

Select FIRSTNAME from PERSON where LEFT(FIRSTNAME,1)='C'

Let's see what happens when looking at the execution plan:

Index is used but the it is scanned and so table is read entirely.
Since SEEK is not done the LEFT function is not SARGABLE.

Now, if the LEFT function is not SARGABLE what could we do?
Well let's try to find one that is.

If we wrote the our query using the LIKE operator?

SELECT FIRSTNAME from PERSON where FIRSTNAME LIKE 'C%'

WOW! This time the SEEK of the index is done and therefore the LIKE operator is SARGABLE!





Ok! That's all for today!

If you like this article and if you think it useful, follow me and we can continue with the topic, maybe looking at what functions are SARGABLE and which are not.

Luca Biondi @ SQLServerPerformance blog!

 

 

 

 

Next post: Sargable Queries part 2. Examples
Previous post: SQL Server 2019 and the Accelerated Database Recovery