SQL Server Prestazioni di SELECT INTO vs INSERT INTO per le tabelle temporanee
Da: Jignesh Raiyani | Aggiornato: 2017-01-04 | Commenti (9) | Correlati: More > T-SQL
Free MSSQLTips Webinar: Migliori pratiche di sviluppo per SQL Server
Partecipa a questo webinar per imparare le migliori pratiche di sviluppo per SQL Server. Andy Warren condividerà i suoi molti anni di esperienza per dare alcune indicazioni su ciò che ha funzionato meglio per lui e come puoi utilizzare alcune di queste conoscenze.
Problema
SQL Server include codice SELECT…INTO e INSERT…INTO per inserire dati in tabelle temporanee. Puoi fornire alcuni esempi e descrivere quale opzione funziona meglio?
Soluzione
QL Server include le due opzioni per le tabelle temporanee:
- Tabella temporanea locale
- Tabella temporanea globale
È necessario aggiungere il prefisso ‘#’ per le tabelle temporanee locali e ‘##’ per quelle globali. Questi oggetti saranno creati nel database di sistema TempDB. Le tabelle temporanee sono memorizzate nel database TempDB con un nome oggetto unico e create a livello di connessione o di sessione. Queste tabelle sono visibili e accessibili solo all’interno della sessione. Possiamo eliminare la tabella temporanea usando il comando DROP TABLE o la tabella temporanea sarà eliminata automaticamente quando la sessione si disconnette. Inoltre, SQL Server mantiene le statistiche per le tabelle temporanee.
La creazione e la cancellazione delle tabelle temporanee richiede l’accesso e la modifica delle pagine della mappa di allocazione di TempDB (IAM, SGAM e PES). Le tabelle temporanee sono memorizzate nel buffer pool rispetto alle tabelle permanenti basate su disco. Con la cache delle tabelle temporanee, SQL Server non abbandona fisicamente la tabella, ma la tronca e mantiene le pagine IAM e dati. Quando la tabella viene creata in seguito, SQL Server riutilizzerà le pagine precedenti, il che riduce il numero di modifiche alle pagine richieste.
Le tabelle temporanee possono essere create in due modi:
- CREATE TABLE
- SELECT INTO
In generale, le prestazioni di entrambe le opzioni sono simili per una piccola quantità di dati. I dati vengono inseriti rapidamente nella tabella temporanea, ma se la quantità di dati è grande, allora possiamo sperimentare scarse prestazioni di query. Questo di solito accade con le tabelle temporanee quando inseriamo un gran numero di righe.
SQL Server INSERT INTO Esempio
SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLCREATE TABLE #list_to_table(Id BIGINT)SELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML) INSERT INTO #list_to_tableSELECT f.x.value('.', 'BIGINT') AS IdFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table
SQL Server parse e compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table '#list_to_table____000000000015'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
Quando c’è una grande quantità di valori separati da virgola come parametro di input, il codice può essere ritardato di qualche secondo durante l’inserimento dei dati nel temptable.
SQL Server SELECT INTO Esempio
Come soluzione alternativa possiamo usare il comando SELECT…INTO che generalmente ha prestazioni migliori del comando INSERT…SELECT.
SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLSELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML)SELECT f.x.value('.', 'BIGINT') AS IdINTO #list_to_tableFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table
SQL Server parse and compile time:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
SQL Server INSERT….SELECT vs. SELECT…INTO con un set di dati più grande
Gli esempi precedenti sono molto piccoli, quindi usiamo l’esempio qui sotto per vedere come funziona con una grande quantità di dati. Questo test avrà oltre un milione di righe.
SQL Server INSERT… SELECT
CREATE TABLE #EMPLOYEEEEE (Emp_id BIGINT, f_name NVARCHAR(100), l_name NVARCHAR(100), Email NVARCHAR(100), is_active BIT) INSERT INTO #EMPLOYEESELECT Emp_id, f_name, l_name, Email, is_active FROM employee
SQL Server parse and compile time:
SQL Server Execution Times: CPU time = 1407 ms, elapsed time = 3241 ms.(1727264 row(s) affected)
SQL Server SELECT…INTO
SELECT Emp_id, f_name, l_name, Email, is_activeeINTO #EMPLOYEEFROM employee
SQL Server parse and compile time:
SQL Server Execution Times: CPU time = 1499 ms, elapsed time = 489 ms.(1727264 row(s) affected)
Risultati e analisi
Come possiamo vedere il SELECT…INTO era considerevolmente più veloce 489ms rispetto a3241ms.
Il comando INSERT…INTO riutilizzerà le pagine di dati che sono create nella cache per le operazioni di inserimento/aggiornamento/cancellazione. Il comando SELECT…INTO creerà nuove pagine per la creazione di tabelle simili alle tabelle regolari e le rimuoverà fisicamente quando la tabella temporanea verrà eliminata.
Passi successivi
- Tenete a mente queste considerazioni sulle prestazioni mentre sviluppate il vostro codice o dovete mettere a punto il codice che non funziona in modo ottimale.
- Testate ulteriori scenari nel vostro ambiente per determinare quale tecnica di codifica funzionerà meglio.
- Avere sempre una mente aperta a varie tecniche di codifica, testare ogni tecnica e lasciare che siano le prestazioni a dettare la risposta.
Ultimo aggiornamento: 2017-01-04
Informazioni sull autore
Jignesh Raiyani è un SQL Server Developer/DBA con esperienza nella progettazione e sviluppo di procedure T-SQL e nell’ottimizzazione delle prestazioni delle query.Vedi tutti i miei consigli
- Altri consigli per sviluppatori di database…