SQL Server Prestaties van SELECT INTO vs INSERT INTO voor tijdelijke tabellen

Probleem

SQL Server bevat SELECT…INTO en INSERT…INTO code voor het invoegen van gegevens in tijdelijke tabellen. Kunt u enkele voorbeelden geven en aangeven welke optie beter presteert?

Oplossing

SQL Server bevat de twee opties voor tijdelijke tabellen:

• Lokale tijdelijke tabel
• Globale tijdelijke tabel

U moet het voorvoegsel ‘#’ toevoegen voor lokale tijdelijke tabellen en ‘##’ voor globale tijdelijke tabellen. Deze objecten zullen worden aangemaakt in de TempDB systeemdatabase. Tijdelijke tabellen worden opgeslagen in de TempDB database met een unieke objectnaam en aangemaakt op connectie- of sessieniveau. Deze tabellen zijn alleen zichtbaar en toegankelijk binnen de sessie. We kunnen de tijdelijke tabel verwijderen met het DROP TABLE commando of de tijdelijke tabel wordt automatisch verwijderd wanneer de sessie wordt verbroken. Bovendien houdt SQL Server statistieken bij voor tijdelijke tabellen.

Het maken en verwijderen van tijdelijke tabellen vereist toegang tot en wijziging van de TempDB allocatie map pagina’s (IAM, SGAM en PES). Tijdelijke tabellen worden gecached in een buffer pool in vergelijking met permanente schijfgebaseerde tabellen. Bij het cachen van tijdelijke tabellen zal SQL Server de tabel niet fysiek laten vallen, maar zal deze trunceren en de IAM en data pagina’s behouden. Wanneer de tabel later wordt gemaakt, zal SQL Server de eerdere pagina’s hergebruiken, waardoor het aantal benodigde pagina modificaties afneemt.

Tijdelijke tabellen kunnen op twee manieren worden gemaakt:

• CREATE TABLE
• SELECT INTO

In het algemeen zijn de prestaties van beide opties vergelijkbaar voor een kleine hoeveelheid gegevens. Gegevens worden snel ingevoegd in de tijdelijke tabel, maar als de hoeveelheid gegevens groot is, kunnen we slechte query-prestaties ervaren. Dit gebeurt meestal met tijdelijke tabellen wanneer we een groot aantal rijen invoegen.

SQL Server INSERT INTO Voorbeeld

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- en compileertijd:

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. 

Wanneer er een groot aantal door komma’s gescheiden waarden als invoerparameter wordt gebruikt, kan de code een paar seconden vertraging oplopen bij het invoegen van de gegevens in de verleidelijke.

SQL Server SELECT INTO Voorbeeld

Een alternatieve oplossing die we kunnen gebruiken is het SELECT…INTO commando dat over het algemeen beter presteert dan het INSERT…SELECT commando.

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- en compileertijd:

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 met een grotere dataset

De bovenstaande voorbeelden zijn erg klein, dus laten we het onderstaande voorbeeld gebruiken om te zien hoe dit presteert met een grote hoeveelheid data. Deze test zal meer dan een miljoen rijen hebben.

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- en compileertijd:

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- en compileertijd:

SQL Server Execution Times: CPU time = 1499 ms, elapsed time = 489 ms.(1727264 row(s) affected)

Resultaten en analyse

Zoals we kunnen zien was de SELECT…INTO aanzienlijk sneller was 489ms vergeleken met3241ms.

Het INSERT…INTO commando zal data pagina’s hergebruiken die in de cache zijn aangemaakt voorinsert/update/delete operaties. De SELECT…INTO opdracht maakt nieuwe pagina’s aan voor het maken van een tabel, vergelijkbaar met regulartables, en verwijdert deze wanneer de tijdelijke tabel wordt verwijderd.

Volgende stappen

• Houd deze prestatieoverwegingen in gedachten wanneer u uw code ontwikkelt of code moet afstellen die niet optimaal presteert.
• test extra scenario’s in je omgeving om te bepalen welke codeertechniek het beste zal presteren.
• sta altijd open voor verschillende codeertechnieken, test elke techniek en laat de prestaties het antwoord dicteren.

Laatst bijgewerkt: 2017-01-04

Over de auteur

Jignesh Raiyani is een SQL Server Ontwikkelaar/DBA met ervaring in ontwerp en ontwikkeling van T-SQL procedures en query performance tuning.
Bekijk al mijn tips

Gerelateerde bronnen

• Meer Database Developer Tips…