SQL Server queries, write them Sargable: the ISNULL function

 Hi Guys,

Welcome back to this blog.

This will be a light post just to review some important concepts that i have already discussed in the past.

We talked about the Sargable concept here:

• Write high performing query: Is your query SARGABLE?
• Sargable Queries part 2. Examples 
  

In shoert, your query is sargable when it can use index by doing a SEEK (and not a SCAN) operation. This way you can read from a table only the specific rows  you need.

This approach is also the only  one that leads to scalable performance.

We have already seen that functions are not Sargable and we have seen some examples and therefore our ISNULL function is also not!

So how can we proceed? so how can we write the same logic in a sargable way?

How to replace the ISNULL function using Sargable logic

Suppose you are reading data from a table. 

Table in our example is JDT1, a standard Journal Entry Table from SAP ONE.

You need to extract rows where the column SourceId is equal to 420.

Now if you use the ISNULL function:

SELECT SOURCEID FROM JDT1 WHERE ISNULL(SOURCEID,0) = 420

You will read 23 pages of data (a data page is 8 KB)

       
(8 tows affected)
Table 'JDT1'. Scan count 1, logical reads 23, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)
Completion time: 2021-09-26T14:22:58.6954595+02:00

Looking at the execution plan we see that an index defined on the SourceID column is used.

However, it is used in SCAN and not in SEEK!

And you noticed that you read 9011 rows just to extract 8!

Attention: the whole table is read and with the passing of time it will increase in the number of rows. And if more users execute this same query together we will have concurrency and scalability problems!

A sargable way...

We could simply write:

SELECT SOURCEID FROM JDT1 WHERE SOURCEID = 420 OR (SOURCEID IS NULL AND 420 = 0)

Executing this Query we read only 2 pages.

(8 rows affected)
Table 'JDT1'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)
Completion time: 2021-09-26T16:30:19.5067209+02:00

This time the index is used in SEEK mode! ...we read 8 rows and extract 8 rows! very well!

A reflection, which I believe to be very important, on competition:

Let's think about this case:
You, like your other colleagues, are working on your documents (delivery notes, invoices, orders, etc etc).

We therefore think of documents as a header table and a row table. We have many order rows for a single header. Suppose the two tables are linked through the Idheader field present on the row table.

If we search for our order rows through the idheader field using an index in SEEK, I will only read my rows. This is very important: I'm not going to read rows that a colleague of mine is working on. This is concurrency.
So it is not if I write a non-sargable query than an index in SCAN.

 

Another way to be sargable..

During a course I held some time ago I was presented with this solution. Well try it, I must say that it is very elegant!

SELECT SOURCEID FROM JDT1
WHERE EXISTS(SELECT SOURCEID INTERSECT SELECT 420);

 

That's all for today! I hope I have caught your attention!
If so, keep following my next posts!

Have a good week ahead!

Luca

Next post:

Previous post: SQL Server, Using the OPTIMIZE FOR SEQUENTIAL KEY Clause to reduce the last page insert latch contention