Di sottoquery ed ottimizzazioni (parte 2)

Ciao a tutti,

Siete pronti per la seconda parte?
Nell'articolo di ieri (clikka qui: Di sottoquery ed ottimizzazioni (parte 1)  ) avevamo parlato di Sottoquery.

Abbiamo visto cosa comportano e che per quanto possibile è bene evitarle.

Già, ma come fare? Lo vediamo adesso! 

P.S. Buona lettura!

 

Di sottoquery ed ottimizzazioni parte 2

Partiamo innanzitutto dalla stringa SQL che avevamo analizzato:

       
SELECT
  Tes.ID, Tes.DataOrdine,
  (SELECT MAX(Det.Prezzo) FROM Dettaglio AS Det WHERE Tes.Id = Det.Idtestata) AS MaxPrezzo,
  (SELECT MAX(Det.Prezzo2) FROM Dettaglio AS Det WHERE Tes.Id = Det.Idtestata) AS MaxPrezzo2,
  (SELECT MAX(Det.Prezzo3) FROM Dettaglio AS Det WHERE Tes.Id = Det.Idtestata) AS MaxPrezzo3,
  (SELECT MAX(Det.Prezzo4) FROM Dettaglio AS Det WHERE Tes.Id = Det.Idtestata) AS MaxPrezzo4
FROM Testata AS Tes

Abbiamo capito, dal piano di esecuzione effettivo che la lettura della tabella Dettaglio viene eseguita 4 volte.

Abbiamo anche visto che, nel nostro caso, vengono lette ben 10200 pagine do memoria da 8 KBytes ed il costo stimato della Query è pari a 0,55.

Noi invece vogliamo fare in modo di leggere la tabella dettaglio una sola volta.

Come potremmo fare? 

Potremmo, per prima cosa, provare utilizzando le CTE di cui abbiamo parlato qui (  Le Common Table Expression (CTE) che cosa sono? )

La stessa Query io l'ho scritta così:

       
WITH CTE_CALCOLO AS (SELECT MAX(Det.Prezzo) AS MaxPrezzo,MAX(Det.Prezzo2) AS MaxPrezzo2,MAX(Det.Prezzo3) AS MaxPrezzo3,Idtestata
                     FROM Dettaglio det
                     GROUP BY Idtestata
                     )
SELECT
  Tes.ID, Tes.DataOrdine,
  (Select CTE_CALCOLO.MaxPrezzo from CTE_CALCOLO Where Tes.Id = CTE_CALCOLO.Idtestata),
  (Select CTE_CALCOLO.MaxPrezzo2 from CTE_CALCOLO Where Tes.Id = CTE_CALCOLO.Idtestata),
  (Select CTE_CALCOLO.MaxPrezzo3 from CTE_CALCOLO Where Tes.Id = CTE_CALCOLO.Idtestata)
FROM Testata AS Tes
 

Analizziamola per vedere cosa succede.

Il numero di pagine lette rimane uguale

mentre il piano di esecuzione ci dice che la Query sulla tabella Dettaglio viene comunque eseguita 4 volte.

Quindi NO! la CTE in questo caso non porta nessun beneficio e non risolve il nostro caso!

Dobbiamo provare qualcosa di diverso!

Proviamo utilizzando una OUTER APPLY:

La nostra interrogazione diventerà potremmo scriverla in questo modo:

SELECT
  Tes.ID, Tes.DataOrdine,
  MAX(MAXPREZZO), MAX(MAXPREZZO2), MAX(MAXPREZZO3), MAX(MAXPREZZO4)
FROM Testata AS Tes
OUTER APPLY (SELECT (Det.Prezzo) AS MAXPREZZO,(Det.Prezzo2) AS MAXPREZZO2,(Det.Prezzo3) AS MAXPREZZO3,(Det.Prezzo4) AS MAXPREZZO4
             FROM Dettaglio Det
             WHERE Tes.Id = Det.Idtestata) t
GROUP BY tes.id,Tes.DataOrdine

Questa volta il numero di pagine lette si è ridotto (non a caso) ad un quarto del valore iniziale:

Ancora più interessante è il piano di esecuzione:



Notate qualcosa?

Certamente si, adesso la tabella Dettaglio viene letta una sola volta.

Ottimo abbiamo trovato la soluzione ottimale ed abbiamo capito qualcosa di importante che voglio ripetere:

Attenzione alle sottoquery sia che siano poste nella SELECT che nella WHERE.
Provate di far leggere a SQL sempre il minore numero di pagine dacchè il tempo di esecuzione è in genere proporzionale al numero di pagine lette.
Al posto di una sottoquery potete utilizzare una OUTER oppure una CROSS APPLY.

State solo attenti a come l'operatore APPLY viene trasformato in una JOIN: nel caso appena visto l'operatore di JOIN utilizzato è un HASH MATCH. A volte accade che l'operatore utilizzato sia un NESTED LOOP anche se il numero di righe da processare non è molto contenuto ma ciò non è assolutamente un bene ai fini del prestazioni.. Ma ne parleremo più avanti...

Per oggi è tutto!

Vi saluto e mi raccomando iscrivetevi per non perdervi proprio nessuno dei prossimi ( spero sempre interessanti ) post.

Ciao!

Luca

Luca Biondi @ SQLServerPerformance blog!

Next post: Trigger e la funzione UPDATE()

Previous post: Di sottoquery ed ottimizzazioni (parte 1)