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!

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)