Confronto tra "Temp Tables" (#) e "Table Variables" (@)
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)
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)
// 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_#
SELECT DESCRIPTION,* FROM FN_DBLOG(NULL,NULL)
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 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)
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
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
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)
Comments
Post a Comment