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