Quando dovrei usare una tabella variabile vs una tabella temporanea in SQL Server?

Sto imparando più dettagli nella variabile di tabella. Dice che le tabelle temporanee sono sempre sul disco e che le variabili di tabella sono in memoria, vale a dire che le prestazioni della variabile di tabella sono migliori della tabella temporanea perché la variabile di tabella utilizza meno operazioni di I / O rispetto alla tabella temporanea.

Ma a volte, se ci sono troppi record in una variabile di tabella che non possono essere contenuti in memoria, la variabile di tabella sarà messa su disco come la tabella temporanea.

Ma non so cosa siano i “troppi record”. 100.000 record? o 1000.000 record? Come posso sapere se una variabile di tabella che sto usando è in memoria o è su disco? Esiste una funzione o uno strumento in SQL Server 2005 per misurare la scala della variabile di tabella o per farmi sapere quando la variabile di tabella viene inserita in memoria dalla memoria?

    La tua domanda mostra che hai ceduto ad alcuni dei malintesi comuni che circondano le variabili della tabella e le tabelle temporanee.

    Ho scritto una risposta abbastanza ampia sul sito di DBA osservando le differenze tra i due tipi di object. Questo affronta anche la tua domanda su disco vs memoria (non ho visto alcuna differenza significativa nel comportamento tra i due).

    Per quanto riguarda la domanda nel titolo se per quanto riguarda quando utilizzare una variabile di tabella rispetto a una tabella temporanea locale non è sempre ansible scegliere. Nelle funzioni, ad esempio, è ansible utilizzare solo una variabile di tabella e se è necessario scrivere nella tabella in un ambito figlio, verrà #temp solo una tabella #temp (i parametri con valori di tabella consentono l’ accesso in sola lettura ).

    Dove hai una scelta, alcuni suggerimenti sono sotto (anche se il metodo più affidabile è semplicemente testare entrambi con il tuo carico di lavoro specifico).

    1. Se hai bisogno di un indice che non può essere creato implicitamente attraverso un vincolo UNIQUE o PRIMARY KEY , allora hai bisogno di una tabella #temporary quanto non è ansible crearli sulle variabili di tabella. (Esempi di tali indici sono non univoci, indici filtrati o indici con le colonne INCLUDE d). NB: SQL Server 2014 consentirà di dichiarare gli indici non univoci in linea per le variabili di tabella.
    2. Se si aggiungono e si eliminano ripetutamente un numero elevato di righe dalla tabella, utilizzare una tabella #temporary . Supporta TRUNCATE (che è più efficiente di DELETE per le tabelle di grandi dimensioni) e inoltre gli inserimenti successivi che seguono un TRUNCATE possono avere prestazioni migliori rispetto a quelli che seguono un DELETE come illustrato qui .
    3. Se si eliminano o aggiornano un numero elevato di righe, la tabella temporanea potrebbe funzionare molto meglio di una variabile di tabella, se è ansible utilizzare la condivisione di set di righe (vedere “Effetti della condivisione di set di righe” di seguito per un esempio).
    4. Se il piano ottimale che utilizza la tabella varia a seconda dei dati, utilizzare una tabella #temporary . Ciò supporta la creazione di statistiche che consente di ricompilare dynamicmente il piano in base ai dati (sebbene per le tabelle temporanee memorizzate nella cache in stored procedure il comportamento di ricompilazione debba essere compreso separatamente).
    5. Se il piano ottimale per la query che utilizza la tabella è improbabile che cambi mai, puoi prendere in considerazione una variabile di tabella per saltare il sovraccarico della creazione di statistiche e ricompilare (probabilmente richiederebbe suggerimenti per correggere il piano che desideri).
    6. Se l’origine dei dati inseriti nella tabella proviene da un’istruzione SELECT potenzialmente costosa, considera che l’utilizzo di una variabile di tabella bloccherà la possibilità di utilizzare un piano parallelo.
    7. Se sono necessari i dati nella tabella per sopravvivere a un rollback di una transazione utente esterna, utilizzare una variabile di tabella. Un ansible caso d’uso per questo potrebbe essere la registrazione dei progressi di diversi passaggi in un lungo batch SQL.
    8. Quando si utilizza una tabella #temp all’interno di una transazione utente, i blocchi possono essere trattenuti più a lungo rispetto alle variabili di tabella (potenzialmente fino alla fine della transazione o alla fine dell’istruzione in base al tipo di blocco e livello di isolamento) e possono anche impedire il troncamento della transazione tempdb registra fino al termine della transazione dell’utente. Quindi questo potrebbe favorire l’uso di variabili di tabella.
    9. All’interno di routine memorizzate, sia le variabili di tabella che le tabelle temporanee possono essere memorizzate nella cache. La manutenzione dei metadati per le variabili della tabella memorizzate nella cache è inferiore a quella delle tabelle #temporary . Bob Ward sottolinea nella sua presentazione tempdb che ciò può causare contesa aggiuntiva sulle tabelle di sistema in condizioni di concorrenza elevata. Inoltre, quando si gestiscono piccole quantità di dati questo può fare una differenza misurabile per le prestazioni .

    Effetti della condivisione di set di righe

     DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT); CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT); INSERT INTO @T output inserted.* into #T SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0 FROM master..spt_values v1, master..spt_values v2 SET STATISTICS TIME ON /*CPU time = 7016 ms, elapsed time = 7860 ms.*/ UPDATE @T SET Flag=1; /*CPU time = 6234 ms, elapsed time = 7236 ms.*/ DELETE FROM @T /* CPU time = 828 ms, elapsed time = 1120 ms.*/ UPDATE #T SET Flag=1; /*CPU time = 672 ms, elapsed time = 980 ms.*/ DELETE FROM #T DROP TABLE #T 

    Utilizzare una variabile di tabella se per una quantità molto piccola di dati (migliaia di byte)

    Utilizza una tabella temporanea per molti dati

    Un altro modo di pensarci: se pensi che potresti trarre beneficio da un indice, statistiche automatizzate o qualsiasi bontà di SQL Optimizer, il tuo set di dati è probabilmente troppo grande per una variabile di tabella.

    Nel mio esempio, volevo solo mettere circa 20 righe in un formato e modificarle come gruppo, prima di usarle per UPDATE / INSERT una tabella permanente. Quindi una variabile di tabella è perfetta.

    Ma sto anche eseguendo SQL per eseguire il back-fill di migliaia di righe alla volta, e posso sicuramente dire che le tabelle temporanee funzionano molto meglio delle variabili di tabella.

    Questo non è diverso dal modo in cui le CTE sono una preoccupazione per un motivo di dimensioni simili – se i dati nel CTE sono molto piccoli, trovo che un CTE abbia prestazioni pari o migliori di quelle fornite dall’ottimizzatore, ma se è piuttosto grande allora ti fa male

    La mia comprensione si basa principalmente su http://www.developerfusion.com/article/84397/table-variables-v-temporary-tables-in-sql-server/ , che ha molti più dettagli.

    Microsoft dice qui

    Le variabili di tabella non hanno statistiche di distribuzione, non attiveranno ricompilazioni. Pertanto, in molti casi, l’ottimizzatore creerà un piano di query presupponendo che la variabile di tabella non abbia righe. Per questo motivo, dovresti essere cauto nell’usare una variabile di tabella se ti aspetti un numero maggiore di righe (superiore a 100). Le tabelle temporanee potrebbero essere una soluzione migliore in questo caso.

    Sono totalmente d’accordo con Abacus (mi dispiace – non ho abbastanza punti per commentare).

    Inoltre, tieni presente che non si tratta necessariamente di quanti record hai, ma della dimensione dei tuoi record.

    Ad esempio, hai considerato la differenza di rendimento tra 1.000 record con 50 colonne ciascuno contro 100.000 record con solo 5 colonne ciascuno?

    Infine, forse stai interrogando / memorizzando più dati del necessario? Ecco una buona lettura delle strategie di ottimizzazione SQL . Limita la quantità di dati che stai tirando, soprattutto se non stai utilizzando tutto (alcuni programmatori SQL diventano pigri e selezionano tutto anche se usano solo un piccolo sottoinsieme). Non dimenticare che l’analizzatore di query SQL potrebbe anche diventare il tuo migliore amico.

    La tabella delle variabili è disponibile solo per la sessione corrente, ad esempio, se è necessario EXEC un’altra stored procedure all’interno di quella corrente, sarà necessario passare la tabella come Table Valued Parameter e ovviamente ciò influirà sulle prestazioni, con tabelle temporanee che è ansible fallo con solo il nome temporaneo della tabella

    Per testare una tabella temporanea:

    • Apri l’editor di query dello studio di gestione
    • Crea una tabella temporanea
    • Apri un’altra finestra di editor di query
    • Seleziona da questa tabella “Disponibile”

    Per testare una tabella variabile:

    • Apri l’editor di query dello studio di gestione
    • Crea una tabella variabile
    • Apri un’altra finestra di editor di query
    • Seleziona da questa tabella “Non disponibile”

    Qualcos’altro che ho sperimentato è: Se il tuo schema non ha il privilegio GRANT per creare tabelle allora usa tabelle variabili. Inoltre, le tabelle temporanee situate nel database tempdb e le variabili di tabella possono trovarsi sia nella memoria che nel disco del database tempdb. Vedi qui: https://www.linkedin.com/pulse/highlighted-differences-between-sql-server-temporary-tables-andrew

    scrivendo i dati nelle tabelle dichiarate declare @tb e dopo declare @tb unito ad altre tabelle, mi sono reso conto che il tempo di risposta rispetto alle tabelle temporanee tempdb .. # tb è molto più alto.

    Quando mi unisco a loro con @tb, il tempo è molto più lungo per restituire il risultato, a differenza di #tm , il ritorno è quasi istantaneo.

    Ho fatto dei test con 10.000 righe e unirmi con altre 5 tabelle