Confronto tra "Temp Tables" (#) e "Table Variables" (@)

Ciao a tutti!

Tempo fa mi avevano chiesto quali fossero le differenze tra le tabelle temporanee e le tabelle in memoria.

Questo articolo l’ho scritto con l’intento di fare chiarezza!

Spero di esserci riuscito.

Oggi vi volevo presentare un confronto tra le tabelle temporanee (#) e le variabili di tipo tabella (@).

 

Analizzeremo nel dettaglio le differenza tra questi due tipologie di tabelle per capire quando è meglio utilizzarne un tipo piuttosto che un altro.

 

Ma non solo, avrete occasione per vedere come leggere il log delle transazioni ed andremo anche a  sfruttare uno strumento che dovrà far parte della vostra “cassetta degli strumenti” e che utilizzeremo per generare il carico di lavoro parallelo necessario per la nostra analisi

Parliamo di tabelle temporanee perché capita spesso nelle nostre elaborazioni di aver bisogno di una tabella “intermedia” sulla quale “appoggiare” i dati.

SQL Server come dicevamo in apertura dell’articolo prevede ben due tipi di tabelle temporanee.
E proprio perché temporanee dopo l’utilizzo non avremo bisogno di eliminarle e questo rappresenta sicuramente una bella comodità.

"Temp Tables" (#) vs. "Table Variables" (@) ...LOP

Il primo tipo di tabella è la tabella temporanea  (o “Temp Table” in inglese) e si comporta a tutti gli effetti come una normale tabella SQL con la sola differenza che viene memorizzata all’interno del database di sistema TEMPDB.

Per crearla è sufficiente generare una tabella il cui nome inizia con il carattere #.
Esempio:

CREATE TABLE #ARTICO (CODICE VARCHAR(80), COLORE VARCHAR(80),  POSIZIONE VARCHAR(80))
       

Il secondo tipo di tabella è invece detta variabile di tabella (o “Table Variable” in inglese).
E’ residente in memoria e viene creata con una sintassi diversa:

DECLARE @ARTICOLO TABLE CODICE VARCHAR(80), COLORE VARCHAR (80), POSIZIONE VARCHAR (80))

L’introduzione l’abbiamo fatta quindi Partiamo!

Creiamoci per prima cosa un ambiente di prova sul quale faremo le nostre considerazioni.

Creiamo una tabella ARTICOLI con il suo indice CLUSTERED sul campo ID:

CREATE TABLE ARTICOLI (ID INT IDENTITY (1,1), CODICE VARCHAR(80), COLORE VARCHAR (80), POSIZIONE VARCHAR (80))
CREATE CLUSTERED INDEX IDX_ARTICOLI_ID on ARTICOLI(ID)
    

Poi riempiamola con un po’ di dati.
Io ad esempio ho usato questo metodo ricorsivo e che usa una CTE per riempire la tabella con 1000 righe

WITH CTE AS
  (SELECT 1 AS CODICE, 1 AS DESCR
   UNION ALL
   SELECT CODICE + 1, CODICE + 1 AS DESCR FROM CTE WHERE CODICE < 10000)
INSERT INTO ARTICOLI (CODICE,COLORE)
  SELECT CODICE, DESCR FROM CTE
  OPTION (MAXRECURSION 10000)
   

Infine creiamoci le due Stored procedure sotto che eseguiremo per analizzarne in comportamento. 

// Scrive in variabile di tabella
CREATE PROCEDURE SP_TEMPART_# AS
BEGIN
  CREATE TABLE #ARTICO (CODICE VARCHAR(80) ,COLORE VARCHAR(80) , POSIZIONE VARCHAR(80))

  INSERT INTO #ARTICO(CODICE,COLORE, POSIZIONE)
    SELECT TOP 10  CODICE,COLORE,POSIZIONE  FROM ARTICOLI
END


// Scrive in tabella temporanea
CREATE PROCEDURE SP_TEMPART_@ AS
BEGIN
  DECLARE @ARTICO TABLE (CODICE VARCHAR(80), COLORE VARCHAR(80), POSIZIONE VARCHAR(80))

  INSERT INTO @ARTICO(CODICE,COLORE,POSIZIONE)
    SELECT TOP 10 CODICE,COLORE,POSIZIONE FROM ARTICOLI
END

Adesso il nostro ambiente è pronto!
Iniziamo a vedere cosa accade sotto le quinte

Resettiamo il log delle transazioni ed eseguiamo la prima delle due Stored procedure: 

CHECKPOINT
EXEC TEST_TEMP_TABLE..SP_TEMPART_#

Poi eseguiamo questa interrogazione:

SELECT DESCRIPTION,* FROM FN_DBLOG(NULL,NULL)

Ecco cosa ritorna:

Siete pronti per analizzarlo?

Allora guardate tutte le operazioni che SQL Server effettua.
Io ho Diviso il log in tre blocchi perché sia più leggibile.

Nel primo blocco vediamo che viene creata una tabella e che viene materializzata nel database TEMPDB.

Nel secondo blocco vengono inserite le righe dentro alla tabella appena creata.
Qui ho una riga di tipo LOP_INSERT_ROWS per ogni riga inserita nella tabella temporanea (nel nostro caso 10 righe).

Infine nel terzo blocco la tabella temporanea viene prima svuotata e poi eliminata.
Vedo prima una riga di LOP_DELETE_ROWS per ogni riga che cancello dalla tabella temporanea (nel nostro caso 10)

Poi se vede la cancellazione della tabella stessa.

Per stimare “il peso della nostra operazione” contiamo, in questa sede, solamente il numero di operazioni LOP (LOP significa LOGGED OPERATION) compiute da SQL Server.
Non ci addentreremo nel analizzarle una per una nel dettaglio perché non è questa la finalità dell’articolo.

Supponendo di inserire N righe nella tabella temporanea avrò un costo di:

NUMERO LOP = ( CREAZIONE TABELLA TEMP ) + ( INSERIMENTO RIGHE ) + [ ( CANCELLAZIONE RIGHE ) + ( ELIMINAZIONE TABELLA TEMP ) ] = ( 9 ) + ( N + 2 ) + [ ( N + 2 ) + ( 8 ) ] = 2 * N + 21 = 41 LOP con N=10 e tabella con 3 colonne



Adesso ripetiamo la stessa analisi per la seconda stored procedure.

Quindi, resettiamo nuovamente il log delle transazioni, eseguiamo la stored procedure ed interroghiamo il nostro log.

CHECKPOINT
EXEC TEST_TEMP_TABLE..SP_TEMPART_@
SELECT DESCRIPTION,* FROM FN_DBLOG(NULL,NULL)

Ecco Qua!

Cosa notiamo a prima vista?
Si vede subito che il log contiene meno righe (nel nostro caso 26 al posto di 41).

E che cosa manca?
Manca tutta la parte di creazione della tabella temporanea nel TEMPDB.
Quindi quello che abbiamo detto all’inizio del nostro articolo è corretto: effettivamente la tabella è in memoria e non sul database.

Supponendo sempre di inserire N righe nella tabella temporanea avrò un costo di:

NUMERO LOP = ( INSERIMENTO RIGHE ) + ( CANCELLAZIONE RIGHE ) = ( N + 2 ) + ( N + 4 ) = 2 * N + 6 = 26 LOP con N=10 e tabella con 3 colonne

Prestazioni e statistiche d'attesa

Confrontiamo ora i due tipi di tabelle temporanee analizzandone prestazioni e statistiche di attesa

Per farlo utilizzeremo uno strumento che Microsoft mette a disposizione gratuitamente e che si chiama RML UTILS. Non ne spiegheremo qui il funzionamento:  diremo solamente che questo strumento permette l’esecuzione di uno statement T-SQL oppure di una Stored procedure in parallelo generando così un carico di lavoro. All’atto pratico richiamiamo un eseguibile che si chiama OStress.exe al quale vanno passati alcuni parametri. OStress.exe –i<NOME_FILE_CON_SQL_ESEGUIRE> -U<UTENTE> –P<PASSWORD> –S<NOMESERVER> -d<> –o<CARTELLA_LOG> -n<X> -r<Y> Da ricordare: Il parametro “-i <NOME_FILE_CON_SQL_ESEGUIRE>” dove <file> è un file che contiene il testo del comando T-SQL o SP da eseguire Il parametro “-n <X>” dove il valore X specificato è il numero di esecuzioni concorrenti della nostra procedura. Il parametro “-r <Y>” dove il valore Y specificato è il numero di volte che l’esecuzione concorrente viene eseguita.

Per analizzare le prestazioni valuteremo solamente il tempo di esecuzione (parametri n=400 e r=20)
Per analizzare i tipi di statistiche d’attesa (i WAIT STATS), sarà necessario, prima della esecuzione, azzerarle tramite il comando DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); 


Testiamo l’inserimento in tabella temporanea (#)

Digitiamo il comando CMD.EXE
Vogliamo eseguire la nostra Stored procedure parallelamente su 400 Thread ripetendo il tutto per 20 volte
Quindi da linea di comando digitiamo:

OStress.exe -ic: \OStress\SP_test_temptable\testtemptable_#.SQL -UAdmin -PPwd -SSERVER -dtest_temp_table -oc:\ostress\SP_test_temptable -n400 -r20
      

Premiamo invio …

Otteniamo un tempo di esecuzione di 6.276 secondi

Osserviamo ora le statistiche di attesa.

Notiamo che sono presenti attese di tipo PAGELATCH sia EXCLUSIVE (EX) che SHARED (SH)

 

Vediamo invece l’inserimento in variabile tabella (@)

Otteniamo un tempo di esecuzione di 5.496 secondi
Osserviamo ora le statistiche di attesa.

Che differenze troviamo?

Il tempo di esecuzione è minore ma soprattutto nelle statistiche di attesa non abbiamo wait type di tipo PAGELATCH_XX e LATCH_XX
Questo ovviamente accade perché i dati non sono materializzati sul database

Quale tipo di tabella di appoggio è meglio utilizzare?

Quando il numero di record che abbiamo necessità di inserire nel tabella temporanea sono un numero limitato è preferibile utilizzare le variabili tabella (@).
L’esecuzione è leggermente più veloce ma soprattutto essendo i dati in memoria evitiamo di materializzarli sul database TempDB.
Evitiamo quindi di generare tipi di attesa di tipo PAGELATCH.

Se invece i dati da inserire sono in numero notevole ci si può orientare sulla tabella temporanea (#)
In questo caso potremo aggiungere un indice per accedere i dati in modo più rapido.
Ricordo infatti che le variabili tabella non supportano per ovvi motivi l’indicizzazione.

Ciao, alla prossima!

Luca Biondi @ SQLServerPerformance blog!

Next post: Installare ed utilizzare le RML Utilities per SQL Server. Come generare un carico di lavoro!
Previous post: Ultimo tentativo di accesso a SQL e la "Dedicated Admin Connection" (DAC)