Tuning di query con tabelle temporanee

In questo articolo analizziamo vediamo un esempio di utilizzo delle tabelle temporanee per migliorare le performance di una query su SQL Server. Le tabelle temporanee sono salvate all’interno del TempDB, questo Database utilizza uno spazio di memoria molto delicato per almeno due motivi:

è condiviso dall’intera istanza di SQL Server e non solo da un singolo Database;
è utilizzato continuamente da SQL Server sia per eseguire task di sistema e sia per risolvere parte delle comuni query SQL lanciate dagli sviluppatori o utenti.

Di conseguenza ogni volta che vogliamo usare le tabelle temporanee, dobbiamo accertarci che l’output che la tabella che andremo a creare abbiamo un modesto numero di righe (o comunque ragionevolmente piccolo in base a quella che è la configurazione del TempDb e del disco su cui è salvato).

Relazione tra piano d’esecuzione e statistiche

Supponiamo di avere una tabella con una distribuzione molto particolare di dati in una colonna:

un unico valore è presente più del 99% delle volte;
qualche altro valore si divide il restante 1% delle righe;
sulla colonna è presente un indice non clustered.

Per facilità supponiamo che si tratta della tabella dei Clienti, che la colonna in questione sia il Nome e che il valore ripetuto più volte sia Nicola. Ci aspettiamo per queste due query molto simili dei piani d’esecuzione differenti:

SELECT *
FROM  dbo.Clienti
WHERE Nome = 'Nicola';
SELECT *
FROM  dbo.Clienti
WHERE Nome = 'Alberto';

Infatti nel primo caso SQL Server stima la presenza di 9802 righe che rispettano il filtro, di conseguenza sceglierà di eseguire una semplice scansione dell’intera tabella (l’indice clustered). Nel secondo caso invece stima la presenza di 1 sola riga, di conseguenza userà un indice (non covered) per poi ricavare tutte le altre informazioni con una key lookup (se non conosci questo lessico puoi guardare il mio video corso gratuito sui piani d’esecuzione).

In entrambi i casi le stime sono corrette e il piano d’esecuzione è quello ottimale. Ma cosa succede se il valore con cui eseguire il filtro non è esplicitato direttamente, ma è calcolato tramite una subquery?

Consideriamo ad esempio la query

SELECT *
FROM dbo.Clienti
WHERE Nome = (SELECT TOP 1 Nome
              FROM dbo.Clienti
              ORDER BY Cognome DESC);

SQL Server crea il piano d’esecuzione prima di eseguire la subquery, di conseguenza non conosce a priori quale sia il relativo risultato. Di conseguenza è possibile che nella costruzione del piano utilizzi una stima non precisa del numero di righe che rispettano la condizione.

Ho racchiuso in blu la parte relativa alla subquery e in rosso la stima utilizzata per la parte finale della query. Come vedete in questo caso risulta sbagliata: 4901 contro 1 sola riga reale. Personalmente non conosco con quale regola sia stato scelto il valore 4901, noto però che in questo caso è circa pari alla metà delle righe complessive della tabella.

Utilizzo delle tabelle temporanee per il tuning

L’idea di ottimizzazione è quella di materializzare il risultato della sub-query in una tabella temporanea e di creare dunque due query distinte. In questo caso siamo sicuri di poter utilizzare tranquillamente una tabella temporanea senza creare problemi al TempDB, in quanto l’output della sub-query sarà per costruzione sempre costituito da una sola riga. Speriamo infatti che in questo modo SQL Server utilizzi il valore presente all’interno della tabella temporanea per eseguire una stima più corretta.

Tuttavia, con un po’ di delusione, la parte relativa al filtro sul nome non cambia né per quanto riguarda il piano d’esecuzione, né per quanto riguarda le stime.

SELECT TOP 1 Nome
INTO #TopNome
FROM Clienti
ORDER BY Cognome DESC;
SELECT *
FROM  dbo.Clienti
WHERE Nome = (SELECT Nome
              FROM #TopNome);

La situazione invece cambia sostituendo l’operatore uguale (“=”) con l’operatore IN. Nel caso esaminato le due query sono completamente equivalenti perché la tabella temporanea sarà sempre costituita da una sola riga (in generale, se fossero presenti più righe la query con l’uguale solleverebbe un errore). Riscriviamo dunque la seconda query in questo modo

SELECT * 
FROM   dbo.Clienti 
WHERE  Nome IN (SELECT Nome 
                FROM #TopNome);

e otteniamo un piano d’esecuzione ottimizzato, creato stimando correttamente una sola riga di ouput.

Osserviamo in particolare che il valore nella tabella temporanea viene prima cercato in maniera ottimizzata all’interno dell’indice IX_clienti_nome e poi la query è “completata” tramite una Key Lookup per estrarre anche le colonne non presenti nell’indice.

Continua a imparare

Leggi gli altri articoli sulle performance dei database
Ripassiamo l’utilizzo delle CTE
Il mio video corso sul performance tuning di SQL Server