Scegliere tra variabili di tabella e tabelle temporanee (ST011, ST012)

Le persone possono, e fanno, discutere molto sui meriti relativi delle variabili di tabella e delle tabelle temporanee. A volte, come quando scrivi funzioni, non hai scelta; ma quando lo farai scoprirai che entrambi hanno i loro usi ed è facile trovare esempi in cui uno dei due è più veloce. In questo articolo, spiegherò i principali fattori coinvolti nella scelta delluno o dellaltro e dimostrerò alcune semplici “regole” per ottenere le migliori prestazioni.

Supponendo che tu segua le regole di base del coinvolgimento , quindi dovresti considerare le variabili di tabella come prima scelta quando lavori con set di dati relativamente piccoli. Sono più facili da lavorare e attivano meno ricompilazioni nelle routine in cui vengono utilizzate, rispetto allutilizzo di tabelle temporanee. Le variabili di tabella richiedono anche meno risorse di blocco poiché sono “private” per il processo e il batch che le ha create. SQL Prompt implementa questa raccomandazione come regola di analisi del codice, ST011 – Prendi in considerazione lutilizzo di una variabile di tabella anziché di una tabella temporanea.

Se stai eseguendo unelaborazione più complessa su dati temporanei o devi utilizzare quantità più che ragionevolmente piccole di dati in essi, è probabile che le tabelle temporanee locali siano una scelta migliore. SQL Code Guard include una regola di analisi del codice, basata sulla sua raccomandazione, ST012 – Prendi in considerazione lutilizzo di una tabella temporanea invece della variabile di tabella, ma attualmente non è implementata nel prompt SQL.

Pro e contro delle variabili di tabella e delle tabelle temporanee

Le variabili di tabella tendono a ricevere “cattiva stampa”, perché le query che le utilizzano occasionalmente risultano in piani di esecuzione molto inefficienti. Tuttavia, se segui alcune semplici regole, sono una buona scelta per tabelle di “lavoro” intermedie e per il passaggio di risultati tra routine, dove i set di dati sono piccoli e lelaborazione richiesta è relativamente semplice.

Le variabili di tabella sono molto semplici da usare, principalmente perché sono “a manutenzione zero”. Sono limitate al batch o alla routine in cui vengono create e vengono rimosse automaticamente una volta completata lesecuzione, quindi utilizzandole allinterno di una connessione di lunga durata non rischia problemi di “consumo di risorse” in tempdb. Se una variabile di tabella viene dichiarata in una procedura memorizzata, è locale rispetto alla procedura memorizzata e non può essere referenziata in una procedura annidata Non ci sono inoltre ricompilazioni basate su statistiche per le variabili di tabella e non è possibile ALTER uno, quindi le routine che le utilizzano tendono a richiedere un minor numero di ricompilazioni rispetto a quelle che utilizzano tabelle temporanee. Inoltre non sono completamente registrate, quindi crearle e riempirle è più veloce e richiede meno spazio nel t registro delle transazioni. Quando vengono utilizzati nelle stored procedure, cè meno contesa sulle tabelle di sistema, in condizioni di elevata concorrenza. In breve, è più facile mantenere le cose pulite e in ordine.

Quando si lavora con set di dati relativamente piccoli, sono più veloci della tabella temporanea comparabile. Tuttavia, man mano che il numero di righe aumenta, oltre le 15.000 righe circa, ma varia a seconda del contesto, è possibile incontrare difficoltà, principalmente a causa della mancanza di supporto per le statistiche. Anche gli indici che applicano i vincoli PRIMARY KEY e UNIQUE alle variabili di tabella non hanno statistiche . Pertanto, lottimizzatore utilizzerà una stima hardcoded di 1 riga restituita da una variabile di tabella, e quindi tenderà a scegliere operatori ottimali per lavorare con piccoli set di dati (come loperatore Nested Loops per i join). Più righe nella variabile della tabella, maggiori sono le discrepanze tra stima e realtà e più inefficienti diventano le scelte del piano dellottimizzatore. Il piano risultante a volte è spaventoso.

Lo sviluppatore esperto o lamministratore di database sarà alla ricerca di questo tipo di problema e sarà pronto ad aggiungere il OPTION (RECOMPILE) suggerimento di query per listruzione che utilizza la variabile di tabella. Quando inviamo un batch contenente una variabile di tabella, lottimizzatore compila prima il batch a quel punto la variabile di tabella è vuota. Quando il batch inizia lesecuzione, il suggerimento provocherà la ricompilazione solo di quella singola istruzione, a quel punto la variabile della tabella verrà popolata e lottimizzatore può utilizzare il conteggio delle righe reali per compilare un nuovo piano per quellistruzione. A volte, ma raramente, anche questo non aiuta. Inoltre, fare eccessivo affidamento su questo suggerimento annullerà in una certa misura il vantaggio che le variabili di tabella hanno di causare un minor numero di ricompilazioni rispetto alle tabelle temporanee.

In secondo luogo, alcune limitazioni di indice con le variabili di tabella diventano più importanti quando si ha a che fare con set di dati di grandi dimensioni. Sebbene ora sia possibile utilizzare la sintassi di creazione dellindice inline per creare indici non cluster su una variabile di tabella, esistono alcune limitazioni e non sono ancora presenti statistiche associate.

Anche con conteggi di righe relativamente modesti, puoi riscontrare problemi di prestazioni delle query se provi a eseguire una query che è un join e dimentichi di definire un PRIMARY KEY o UNIQUE vincolo sulla colonna che stai utilizzando per lunione. Senza i metadati forniti, lottimizzatore non è a conoscenza dellordine logico dei dati o se i dati nella colonna di join contengono valori duplicati e probabilmente sceglierà operazioni di join inefficienti, con conseguenti query lente. Se stai lavorando con un heap di variabili di tabella, puoi utilizzarlo solo come un semplice elenco che è probabile che venga elaborato in un unico gulp (scansione della tabella). Se combini sia luso del OPTION (RECOMPILE) suggerimento, per stime accurate della cardinalità, e una chiave nella colonna join per rendere utile lottimizzatore metadati, quindi per set di dati più piccoli puoi spesso ottenere velocità di query simili o migliori rispetto allutilizzo di una tabella temporanea locale.

Una volta che i conteggi delle righe aumentano oltre la zona di comfort di una variabile di tabella, oppure devi eseguire dati più complessi in fase di elaborazione, è meglio passare a utilizzare tabelle temporanee. Qui hai tutte le opzioni a tua disposizione per lindicizzazione e lottimizzatore avrà il lusso di utilizzare le statistiche per ciascuno di questi indici. Ovviamente, lo svantaggio è che i tavoli temporanei hanno un costo di manutenzione più elevato. Devi assicurarti di chiarire dopo te stesso, per evitare la congestione di tempdb. Se si altera una tabella temporanea o si modificano i dati in essa contenuti, è possibile che venga eseguita la ricompilazione della routine padre.

Le tabelle temporanee sono migliori quando è necessario un numero elevato di eliminazioni e inserimenti (condivisione di set di righe ). Ciò è particolarmente vero se i dati devono essere completamente rimossi dalla tabella, poiché solo le tabelle temporanee supportano il troncamento. I compromessi nella progettazione delle variabili di tabella, come la mancanza di statistiche e ricompilazioni, funzionano contro di loro se i dati sono volatili.

Quando conviene usare le variabili di tabella

Noi Inizierò con un esempio in cui una variabile di tabella è lideale e si traduce in prestazioni migliori. Produrremo un elenco di dipendenti per Adventureworks, in quale reparto lavorano e i turni in cui lavorano. Abbiamo a che fare con un piccolo set di dati (291 righe).

Metteremo i risultati in una seconda tabella temporanea, come se stessimo passando il risultato al batch successivo. Il Listato 1 mostra il codice.

Ed ecco un tipico risultato sulla mia macchina di test lenta:

Luso di una tabella temporanea è costantemente più lento, anche se le singole esecuzioni possono variare molto.

I problemi di scala e dimenticarsi di fornire una chiave o un suggerimento

Comè la performance se uniamo due variabili di tabella? Proviamolo. Per questo esempio, abbiamo bisogno di due semplici tabelle, una con tutte le parole comuni in lingua inglese (CommonWords) e laltra con un elenco di tutte le parole in Dracula di Bram Stoker (WordsInDracula). Il download di TestTVsAndTTs include lo script per creare queste due tabelle e popolare ciascuna di esse dal file di testo associato. Ci sono 60.000 parole comuni, ma Bram Stoker ne ha usate solo 10.000. Il primo è ben al di fuori del punto di pareggio, dove si inizia a preferire le tabelle temporanee.

Useremo quattro semplici query di join esterno, testando il risultato per NULL valori, per scoprire le parole comuni che non sono in Dracula, le parole comuni che sono in Dracula, le parole in Dracula non comuni, e infine unaltra query per trovare parole comuni in Dracula, ma che si uniscono nella direzione opposta. Vedrai le query a breve, quando mostro il codice per il test rig.

Di seguito sono riportati i risultati delle esecuzioni di test iniziali. Nella prima esecuzione, entrambe le variabili di tabella hanno chiavi primarie e nella seconda sono entrambe heap, solo per vedere se sto esagerando i problemi di non dichiarare un indice in una variabile di tabella. Infine, eseguiamo le stesse query con tabelle temporanee. Tutti i test sono stati eseguiti, deliberatamente, su un server di sviluppo lento, a scopo illustrativo; si ottengono risultati molto diversi con un server di produzione.

I risultati mostrano che quando le variabili di tabella sono enormi, si corre il rischio che la query venga eseguita per dieci minuti invece che per 100 millisecondi. Questi danno un ottimo esempio delle prestazioni orribili che puoi sperimentare se non conosci le regole. Anche quando usiamo le chiavi primarie, tuttavia, il numero di righe con cui abbiamo a che fare significa che lutilizzo di tabelle temporanee è ora due volte più veloce.

Non approfondirò i dettagli dei piani di esecuzione dietro questi metriche delle prestazioni, oltre a fornire alcune spiegazioni generali delle principali differenze. Per le query sulla tabella temporanea, lottimizzatore, armato di una conoscenza completa della cardinalità e dei metadati dai vincoli della chiave primaria, sceglie un efficiente operatore Merge Join per eseguire loperazione di join.Per la variabile di tabella con chiavi primarie, lottimizzatore conosce lordine delle righe nella colonna di join e che non contengono duplicati, ma presume che si tratti solo di una riga e quindi sceglie invece un join di cicli annidati. Qui, esegue la scansione di una tabella e quindi per ogni riga restituita esegue ricerche individuali dellaltra tabella. Diventa meno efficiente quanto più grandi sono i set di dati ed è particolarmente dannoso nei casi in cui esegue la scansione della variabile della tabella CommonWords, perché risulta in oltre 60.000 ricerche del Dracula variabile di tabella. Il join di cicli annidati raggiunge il “picco di inefficienza” per due query di dieci minuti utilizzando heap di variabili di tabella, poiché comporta migliaia di scansioni di tabelle di CommonWords. È interessante notare che le due query “parole comuni in Dracula” funzionano molto meglio e questo perché, per queste due, lottimizzatore ha scelto invece un join Hash Match.

Nel complesso, le tabelle temporanee sembrano essere la scelta migliore , ma non abbiamo ancora finito. Aggiungiamo il OPTION (RECOMPILE) suggerimento alle query che utilizzano le variabili di tabella con chiavi primarie e rieseguire i test per queste query e le query originali utilizzando le tabelle temporanee. Per il momento tralasciamo i poveri cumuli.

Come puoi vedere, il vantaggio in termini di prestazioni della tabella temporanea svanisce. Armato di conteggi di righe corretti e input ordinati, lottimizzatore sceglie Merge Join molto più efficiente.

Cosa, ti chiedi, succederebbe se fornissi a quei poveri heap il OPTION (RECOMPILE) suggerimento? Ecco, la storia cambia per loro in modo che tutti e tre i tempi siano molto più vicini.

È interessante notare che le due “parole comuni in Dracula” interrogano che erano veloci anche sui cumuli ora sono molto più lenti. Armato dei conteggi di riga corretti, lottimizzatore cambia la sua strategia, ma poiché non ha ancora a disposizione nessuno dei metadati utili quando definiamo vincoli e chiavi, fa una cattiva scelta. Esegue la scansione dellheap CommonWords, quindi tenta una “aggregazione parziale”, stimando che si aggregherà da 60.000 righe a poche centinaia. Non sa che non ci sono duplicati, quindi infatti non si aggrega affatto e laggregazione e il successivo join si riversano in tempdb.

Il banco di prova

Si noti che questo è il banco di prova nella sua forma finale mostrando un rendimento pressoché uguale per i tre diversi tipi di tabella. Per tornare alloriginale, dovrai rimuovere i OPTION (RECOMPILE) suggerimento.

Listato 2

Conclusioni

Non cè niente di sconsiderato nellusare le variabili di tabella. Offrono prestazioni migliori se utilizzati per gli scopi per i quali sono stati concepiti, e fanno il loro lavaggio. Ad un certo punto, i compromessi che danno loro una prestazione migliore (non innescare ricompilazioni, non fornire statistiche, nessun rollback, nessun parallelismo) diventano la loro rovina.

Spesso, lesperto di SQL Server darà saggi consigli su la dimensione del risultato che causerà problemi per una variabile di tabella. I risultati che ti ho mostrato in questo articolo ti suggeriranno che questo semplifica eccessivamente i problemi. Ci sono due fattori importanti: se hai un risultato di oltre, diciamo, 1000 righe (e questa cifra dipende dal contesto), allora devi avere un PRIMARY KEY o UNIQUE per qualsiasi query che si unisce a una variabile di tabella. A un certo punto, dovrai anche attivare una ricompilazione per ottenere un piano di esecuzione decente, che ha il suo sovraccarico.

Anche in questo caso, le prestazioni possono risentirne, soprattutto se stai eseguendo elaborazioni più complesse , perché lottimizzatore non ha ancora accesso alle statistiche e quindi nessuna conoscenza della selettività di alcun predicato di query. In questi casi, dovrai passare allutilizzo di tabelle temporanee.

Ulteriori letture