SQL Performance: Say no to the "NOT IN" operator

Hello friends,

Paraphrasing the title of the american television show "Say Yes to the dress", our title of today is "Say no to the NOT IN operator".

Why? 

Well, we will show you that the "Not in" operator can be substituited with the "Not Exists" operator and that normally the Not Exists operator is fastest!

How we write a Query ultimately determines how much it will be efficient.
Yes, SQL Server is a declarative language (we tell him what we want to achieve and he internally decides how to get it) but if we help him by applying a series of good rules we will get improvements from the point of view of performance.

But now, as usual, we will show you with an example


Let's go!

For the example we will use our tables ORDRIG and PRODUCTS. Each table have a clustered index on the ID integer field:

       
CREATE CLUSTERED INDEX IDX_ORDRIG_ID ON ORDRIG(ID)
CREATE CLUSTERED INDEX IDX_PRODUCTS_ID ON PRODUCTS(ID)

Well now imagine your boss ask a list of rows of orders whoose description do not contain the word "CARTUC".

You could write the Query below with a NOT IN operator:

       
SELECT * FROM ORDRIG T
WHERE T.DESCR NOT IN
  (SELECT DESCR FROM PRODUCTS WHERE DESCR LIKE 'CARTUC%')

Or you could write the same query with an NOT EXISTS operator:

       
SELECT * FROM ORDRIG T
WHERE T.DESCR NOT EXISTS
  (SELECT 1 FROM PRODUCTS A WHERE T.DESCR = A.DESCR AND A.DESCR LIKE 'CARTUC%')


Yes but what is the difference? The result is the same but the execution plans differ!




Differences

When SQL Server resolve the Query with the NOT IN operator, consider the main part of the Query on the ORDRIG before and the part of the PRODUCTS table after.

For this reason SQL Server creating an execution plan first use a clustered index scan on the ORDRIG table. As a second step use the clustered index scan on the PRODUCTS table.

The type on join choosen in this case is a nested loop!

Using the NOT EXISTS operator the two tables are joined though the DESCR field and SQL Server is free to choose for the execution plan wich order use.

Especially it is free to choose to use as main table the table with less rows.

In our example the table with less rows is the PRODUCTS table while the ORDRIG table is a detail. With the two table arranged in this way SQL SERVER is free to use a more efficent Hash Match Join.

A correct join type is foundament and it is sufficient to look at the pages read to realize it:


With the NOT IN operator we read more than 2 million of pages from the PRODUCT table:

       
Tabella 'Products'. Conteggio analisi 1, letture logiche 2248139, letture fisiche 1, letture read-ahead 2, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
Tabella 'OrdRig'. Conteggio analisi 1, letture logiche 35478, letture fisiche 3, letture read-ahead 35474, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

While using the NOT EXISTS operator we read just 4 pages from the PRODUCT table:
       
Tabella 'OrdRig'. Conteggio analisi 1, letture logiche 35478, letture fisiche 3, letture read-ahead 9353, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
Tabella 'Products'. Conteggio analisi 1, letture logiche 4, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

This is really cool! With the NOT IN operator we get the result in 30 seconds, with the NOT EXISTS operator we get the same result in 20 seconds.

This in another tips and another aspect to take care if you want to write well performing queries!


That's all folks and remember:

If you liked this post then leave a comment, subscribe to the blog and wait for the next post!

Luca












Previous post:SQL Server, A bit of reverse engineering inside the parser

Comments

Post a Comment

I Post più popolari

SQL Server, execution plan and the lazy spool (clearly explained)

Image
Hi Guys, Welcome back! In one of the last posts we talked about Table Spool . We had explained what Spool are for by telling the somewhat curious story of the halloween problem . Here we specifically talked about a type of spool called Eager Spool   You can look to the execution plan below where the eager Spool is used to avoid the halloween problem in classic query of the "salary increase".. i suggest Well, if the story of the eager spool intrigued you, today instead we will speak about another type of Spool, the Lazy Spool . The Lazy spool Let's take a small step back. So, what is a Spool operation?  As we have already understood a spool operation is simply a temporary storage of data . Data are read from a source table and stored inside a worktable in the Tempdb database . While an Eager Spool is used to prevent the Halloween problem, the Lazy Spool is instead used to store data that will later be needed again when running a query . So, when a Lazy spool is used?    I
Read more

SQL Server, datetime vs. datetime2

Image
Hi guys, This time, in this post we will speak about datetime and datetime2 datatypes. We will talk about the pros and cons. We will talk about all the differences between the two types of data. Write me in the comments which type of data do you prefer between the two. Enjoy the reading!     Datetime Vs. Datetime2 First of all, it must be said that the datatype datetime is certainly the best known native format for storing a date in SQL Server. However, Microsoft has also been introducing a new type of data for many years now, also used to manage dates. It is in fact from SQL server 2008 that it is present in datetime2 data type. This "new" type of data was born primarily to have higher accuracy . Let's in fact talk about ... precision ! Datetime Certainly the datetime data type is not very precise and certainly there are many cases in which its precision is simply not sufficient. Let's see it with an example. Open the SQL Management studio (SSMS) and write the fo
Read more

La clausola NOLOCK. Approfondiamo e facciamo chiarezza!

Image
Ehi ragazzi! La domanda è di quelle importanti! Siete pronti per scoprire tutto ma proprio tutto sulla clausola NOLOCK?   Se la riposta è si, allora seguitemi! Oggi ne spiegheremo il funzionamento in maniera approfondita. Faremo una introduzione al concetto di tipi di LOCK Ed infine vedremo come mai una SELECT ne può bloccare un'altra. Non vi nascondo che il motivo di questo articolo è fare chiarezza. Su questo argomento si sentono spesso affermazioni “curiose” ma tra le tante questa è la prima: “ uso la SELECT con l’opzione WITH(LOCK) così non blocco le altre tabelle ”.   Ma, è proprio vero quanto affermato? Beh oggi facciamo il punto!   Buona lettura!     La clausola NOLOCK Diciamo subito per iniziare che cos’è la NOLOCK. NOLOCK è il nome di una clausola che è possibile specificare all’interno di uno statement di tipo SELECT utilizzando la seguente sintassi: SELECT * FROM TABELLA WITH (NOLOCK) Si noti che non possiamo utilizzare questa cl
Read more