SQL Server, A problematic Query with a Nested Loop and a huge list of ID


Cari lettori,

Oggi parliamo ancora una volta di prestazioni analizzando un caso pratico che mi è capitato recentemente.
Solo con una buona conoscenza del funzionamento interno di SQL server possiamo far si che le nostre Query siano il più veloce possibile!


Hi Guys,

Today we speak about performance seeing a real case just occoured to me.
Only by knowing well the inner workings of SQL server we can run our queries as fast as possible!

 

Introduction


Recentemente mi sono trovato di fronte ad questa Query:
 
 

Select 
  A.ID, [...]
From <Tabella> A 
  JOIN 
[...] 
Where 
  A.Id in (4,5, .. <elenco di migliaia di ID>.. ,44174) and
  A.codice Like N'0031537%'

La prima condizione dopo la clausola WHERE conteneva migliaia di valori di ID e la sua esecuzione impiegava ben 20 secondi!

Vediamo di capire perchè questa Query è poco performante?
Per farlo partiamo osserviamo il piano di esecuzione!




Recently I found myself in front of a Query written like this:

       
Select 
  A.ID, [...]
From <Tabella> A 
  JOIN 
[...] 
Where 
  A.Id in (4,5, ..<a list of thousands IDs>.. ,44174) and
  A.codice Like N'0031537%'

Note that the first condition after the WHERE clause contained thousands of ID values and the execution took no less than 20 seconds!

But why is this Query not performing well? 
In order to understand this, let's look at the execution plan!



Let's look to the execution plan ...in detail



Guardiamo il piano di esecuzione per capire come viene risolto il nostro comando T-SQL


Nel nostro comando T-SQL abbiamo le seguenti due condizioni dopo la clausola WHERE:

  1) A.Id in (4,5, ..<elenco di migliaia di ID>.. ,44174) and
  2) A.codice Like N'0031537%'

Vediamo che l'unione di queste due condizioni (AND) avviene tramite l'operatore Nested Loop (evidenziato il giallo).





Mentre la condizione "critica": 

A.Id in (4,5, ..<elenco di migliaia di ID>.. ,44174)

viene invece risolta tramite un operatore Constant Scan seguito da un operatore di Filter.

Se ci fate caso l'operatore di costant scan tratta tutto l'elenco dei nostri ID "come se fosse una tabella con la sola colonna ID".

Lo vediamo bene guardando i valori nella riga values della finestra delle Properties.






L'operatore Filter, cerca poi, all'interno della tabella sopramenzionata di volta in volta il valore cercato.



Avrete quindi capito già dov'è il problema! 


Per via del fatto che viene utilizzato un operatore di tipo Nested Loop (che ricordo adotta una logica di tipo Master/Detail), ne caso in cui abbiamo un elenco di ID, la Query viene risolta ripetendo la ricerca per ognuno degli ID del nostro elenco. E questo non è assolutamente cosa buona!



Per oggi è tutto! 

Siamo alla vigilia di Natale e volevo approfittarne per augurarVi un sereno natale a voi ed a tutta la vostra famiglia. 
Vi auguro possiate approfittare di qualche giorno di relax per ricaricarvi di Energia per affrontare il 2020 con un rinnovato entusiasmo! 

A presto! 








Let's look at the execution plan to understand how our T-SQL command is solved.



In our T-SQL command we have two conditions after the WHERE clause which are:

  1) A.Id in (4,5, ..<list of thousands ID>.. ,44174) and
  2) A.codice Like N'0031537%'


These two conditions are merged (AND) through the Nested Loop operator (highlighted in yellow):






Well, we can see that, the condition 

A.Id in (4,5, .. <list of thousands of IDs> .., 44174)

is resolved through a Constant Scan operator and then a Filter operator.

The constant scan treats the whole list of our IDs "as a table with only the ID column".
We see all the values ​​in the values ​​row of the Properties window.





Then, the Filter operator searches the above mentioned table from time to time for the value sought.


You will therefore have already understood where the problem is!

Remember: Due to the fact that a Nested Loop operator is used (which adopts a Master / Detail logic), if we have a list of IDs, the Query is solved by repeating the search for each of the IDs in our lis! That's not a good things!



That's all for today, 
We are on Christmas Eve and I take this opportunity to wish you a peaceful Christmas to you and your whole family. 
I wish you to take advantage, if you can, of a few days to recharge your energy and to face the 2020 with a renewed enthusiasm!

See you soon!


Luca Biondi @ SQLServerPerformance blog!
Logo Sqlserverperformace


Next post:Draw with SQL Server, plot the Mandelbrot set!

Previous post: SQL Server, Il vostro "super cliente" è bloccato! (MSQL_DQ e PREEMPTIVE_COM_QUERYINTERFACE Wait states)

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?  ...
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 ...
Read more