SQL Server Performance Test: Temporäre Tabelle vs. Tabellenvariable

Was ist besser zu verwenden - eine temporäre Tabelle oder eine Tabellenvariable?

Zu Dieser Frage musste sicherlich schon der ein oder andere Datenbankadministrator Rede und Antwort stehen. Um hierzu eine zufriedenstellende Antwort zu liefern, sollte man wie so oft in der Welt der Informatik, Verwendungszweck und Ziel der Tabelle genau definieren. So gibt es eine Reihe technischer Gründe die sowohl für die eine, als auch für die andere Lösung sprechen.

In diesem Beitrag wollen wir uns der Frage stellen, welche der beiden Lösungen denn performanter ist und weniger Ressourcen verbraucht. Hierfür werden wir einen Direktvergleich zwischen einer temporären Tabelle und einer Tabellenvariable anhand einiger einfacher Szenarien durchführen, um so zumindest in Bezug auf die reine Performance, eine klare Antwort liefern zu können.

Wir verwenden in diesem Test eine SQL Server 2019 Developer Instanz.

Setup

Für unser Testszenario werden wir Daten in vier Tabellen laden, zwei temporäre Tabellen und zwei Tabellenvariablen. Hierbei werden wir jeweils eine der beiden Tabellen mit 2.000 und die andere mit 1.000.000 Datensätzen befüllen. So können wir ebenfalls prüfen, ob sich auch Performance Unterschiede basierend auf der Größe des Datensatzes ergeben.

Das Erstellen sowie das Befüllen der Tabellen erledigen wir mit folgendem T-SQL Skript:

-- Table creation logic
CREATE TABLE #temptable ([col1] [int] NOT NULL primary key,[col2] [int] NULL,
[col3] [int] NULL,[col4] [varchar](50) NULL)

DECLARE @tablevariable TABLE ([col1] [int] NOT NULL primary key,[col2] [int] NULL,
[col3] [int] NULL,[col4] [varchar](50) NULL)

CREATE TABLE #bigtemptable ([col1] [int] NOT NULL primary key,[col2] [int] NULL,
[col3] [int] NULL,[col4] [varchar](50) NULL)

DECLARE @bigtablevariable TABLE ([col1] [int] NOT NULL primary key,[col2] [int] NULL,
[col3] [int] NULL,[col4] [varchar](50) NULL)

-- Create index logic
CREATE NONCLUSTERED INDEX [IX_temptable] ON #temptable ([col2] ASC)
CREATE NONCLUSTERED INDEX [IX_bigtemptable] ON #bigtemptable ([col2] ASC)
 
-- Populate tables
DECLARE @val INT
SELECT @val=1
WHILE @val <= 2000
BEGIN  
   INSERT INTO #temptable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   INSERT INTO @tablevariable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   INSERT INTO #bigtemptable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   INSERT INTO @bigtablevariable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   SELECT @val=@val+1
END
WHILE @val <= 1000000
BEGIN  
   INSERT INTO #bigtemptable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   INSERT INTO @bigtablevariable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   SELECT @val=@val+1
END

Performance Test

Um die Performance unserer neu angelegten Objekte weitreichend zu testen, werden wir für jede der vier grundlegenden DML-Operationen einen Test durchführen. Diese Operationen sind SELECT, INSERT, UPDATE und DELETE. Für jede dieser Operationen werden wir Anweisungen durchführen, die einzelne aber auch mehrere Zeilen betreffen. Hierbei werden wir die Anweisungen so aufteilen, dass jede Operation die Referenzierung von Primärschlüsselspalten und Nicht-Schlüsselspalten testet - sowohl indiziert als auch nicht indiziert. Das gibt im Nachhinein Aufschluss darüber, wie viel Nutzen aus der Indizierung einer Spalte gezogen werden kann.

Die Tests für alle Anweisungen und Tabellen führen wir mithilfe des folgenden T-SQL Skripts durch:

-- simple insert
INSERT INTO #temptable (col1, col2, col3, col4) VALUES (2005,2005,2005,'TEST')
INSERT INTO @tablevariable (col1, col2, col3, col4) VALUES (2005,2005,2005,'TEST')
INSERT INTO #bigtemptable (col1, col2, col3, col4) VALUES (1000005,1000005,1000005,'TEST')
INSERT INTO @bigtablevariable (col1, col2, col3, col4) VALUES (1000005,1000005,1000005,'TEST')

-- select single row where primary key column equals value
SELECT * FROM #temptable where col1=1000
SELECT * FROM @tablevariable where col1=1000
SELECT * FROM #bigtemptable where col1=600000
SELECT * FROM @bigtablevariable where col1=600000

-- select range of rows where primary key column between values
SELECT * FROM #temptable where col1 between 450 and 500
SELECT * FROM @tablevariable where col1 between 450 and 500
SELECT * FROM #bigtemptable where col1 between 100000 and 100500
SELECT * FROM @bigtablevariable where col1 between 100000 and 100500

-- select single row where other indexed column equals value
SELECT * FROM #temptable where col2=1050
SELECT * FROM @tablevariable where col2=1050
SELECT * FROM #bigtemptable where col2=650000
SELECT * FROM @bigtablevariable where col2=650000

-- select range of rows where other indexed column between values
SELECT * FROM #temptable where col2 between 500 and 550
SELECT * FROM @tablevariable where col2 between 500 and 550
SELECT * FROM #bigtemptable where col2 between 150000 and 150500
SELECT * FROM @bigtablevariable where col2 between 150000 and 150500

-- select single row where other nonindexed column equals value
SELECT * FROM #temptable where col3=1100
SELECT * FROM @tablevariable where col3=1100
SELECT * FROM #bigtemptable where col3=700000
SELECT * FROM @bigtablevariable where col3=700000

-- select range of rows where other nonindexed column between values
SELECT * FROM #temptable where col3 between 550 and 600
SELECT * FROM @tablevariable where col3 between 550 and 600
SELECT * FROM #bigtemptable where col3 between 200000 and 200500
SELECT * FROM @bigtablevariable where col3 between 200000 and 200500

-- update single row where primary key column equals value
UPDATE #temptable set col4='TESTUPDATE' where col1=1150
UPDATE @tablevariable set col4='TESTUPDATE' where col1=1150
UPDATE #bigtemptable set col4='TESTUPDATE' where col1=750000
UPDATE @bigtablevariable set col4='TESTUPDATE' where col1=750000

-- update range of rows where primary key column between values
UPDATE #temptable set col4='TESTUPDATE' where col1 between 600 and 650
UPDATE @tablevariable set col4='TESTUPDATE' where col1 between 600 and 650
UPDATE #bigtemptable set col4='TESTUPDATE' where col1 between 250000 and 250500
UPDATE @bigtablevariable set col4='TESTUPDATE' where col1 between 250000 and 250500

-- update single row where other indexed column equals value
UPDATE #temptable set col4='TESTUPDATE' where col2=1200
UPDATE @tablevariable set col4='TESTUPDATE' where col2=1200
UPDATE #bigtemptable set col4='TESTUPDATE' where col2=800000
UPDATE @bigtablevariable set col4='TESTUPDATE' where col2=800000

-- update range of rows where other indexed column between values
UPDATE #temptable set col4='TESTUPDATE' where col2 between 650 and 700
UPDATE @tablevariable set col4='TESTUPDATE' where col2 between 650 and 700
UPDATE #bigtemptable set col4='TESTUPDATE' where col2 between 300000 and 300500
UPDATE @bigtablevariable set col4='TESTUPDATE' where col2 between 300000 and 300500

-- update single row where other nonindexed column equals value
UPDATE #temptable set col4='TESTUPDATE' where col3=1250
UPDATE @tablevariable set col4='TESTUPDATE' where col3=1250
UPDATE #bigtemptable set col4='TESTUPDATE' where col3=850000
UPDATE @bigtablevariable set col4='TESTUPDATE' where col3=850000

-- update range of rows where other nonindexed column between values
UPDATE #temptable set col4='TESTUPDATE' where col3 between 700 and 750
UPDATE @tablevariable set col4='TESTUPDATE' where col3 between 700 and 750
UPDATE #bigtemptable set col4='TESTUPDATE' where col3 between 350000 and 350500
UPDATE @bigtablevariable set col4='TESTUPDATE' where col3 between 350000 and 350500

-- delete single row where primary key column equals value
DELETE FROM #temptable where col1=1300
DELETE FROM @tablevariable where col1=1300
DELETE FROM #bigtemptable where col1=900000
DELETE FROM @bigtablevariable where col1=900000

-- delete range rows where primary key column between values
DELETE FROM #temptable where col1 between 750 and 800
DELETE FROM @tablevariable where col1 between 750 and 800
DELETE FROM #bigtemptable where col1 between 400000 and 400500
DELETE FROM @bigtablevariable where col1 between 400000 and 400500

-- delete single row where other indexed column equals value
DELETE FROM #temptable where col2=1350
DELETE FROM @tablevariable where col2=1350
DELETE FROM #bigtemptable where col2=950000
DELETE FROM @bigtablevariable where col2=950000

-- delete range of rows where other indexed column between values
DELETE FROM #temptable where col2 between 800 and 850
DELETE FROM @tablevariable where col2 between 800 and 850
DELETE FROM #bigtemptable where col2 between 450000 and 450500
DELETE FROM @bigtablevariable where col2 between 450000 and 450500

-- delete single row where other nonindexed column equals value
DELETE FROM #temptable where col3=1400
DELETE FROM @tablevariable where col3=1400
DELETE FROM #bigtemptable where col3=1000000
DELETE FROM @bigtablevariable where col3=1000000

-- delete range of rows where other nonindexed column values
DELETE FROM #temptable where col3 between 900 and 950
DELETE FROM @tablevariable where col3 between 900 and 950
DELETE FROM #bigtemptable where col3 between 500000 and 500500
DELETE FROM @bigtablevariable where col3 between 500000 and 500500

Testergebnisse

INSERT-Anweisungen

Der SQL Profiler Trace der INSERT Anweisungen zeigt, dass die Tabellenvariable die temporäre Tabelle in Sachen Performance, unabhängig von der Größe des temporären Objektes, übertrifft.
Dies ist vermutlich darauf zurückzuführen, dass die temporäre Tabelle einen zusätzlichen Index für die Tabelle selbst hat, der ebenfalls aktualisiert werden muss.

Mainzer Datenfabrik - SQL Server Performance Test: Temporäre Tabelle vs. Tabellenvariable

SELECT-Anweisungen

Betrachten wir nun den SQL Profiler Trace der SELECT Anweisungen. Hier sieht die Situation schon ganz anders aus. Sowohl bei den Abfragen die auf Primärschlüsselspalten basieren, als auch bei denjenigen, die auf nicht indizierten Spalten basieren, erhalten wir die gleiche Performance von beiden Objekten.
Fragen wir allerdings die Zeilen mit der indizierten Spalte der temporären Tabelle ab, sehen wir eine große Performance Steigerung über alle Kennzahlen für die temporäre Tabelle hinweg. Dieser drastische Performance Unterschied kommt daher, dass die abgefragte Spalte in der Tabellenvariable nicht indiziert ist. Diese Option ist für Tabellenvariablen nicht verfügbar.
Unabhängig von der Größe der temporären Tabelle haben Abfragen der indizierten Spalte fast dieselbe Performance.

UPDATE-Anweisungen

Bei den UPDATE Anweisungen können wir einen Trend ähnlich der SELECT Anweisungen beobachten. Beim Aktualisieren von Zeilen, basierend auf dem Primärschlüssel sowie auf der nicht indizierten Spalte liefern sie performanceseitig sehr ähnliche Ergebnisse. Beim Aktualisieren von Zeilen, basierend auf der indizierten Spalte, schneidet die temporäre Tabelle deutlich besser ab.

DELETE-Anweisungen

Das Ergebnis der DELETE Anweisungen ist sehr interessant. Hier übertrifft die Tabellenvariable die temporäre Tabelle beim Löschen von Zeilen, die auf dem Primärschlüssel basieren. Dies liegt vermutlich, genauso so wie bei den INSERT Anweisungen daran, dass ein zusätzlicher Index aktualisiert werden muss.
Wie bei den anderen Szenarien schneidet die temporäre Tabelle beim Löschen der indizierten Spalte besser ab als die Tabellenvariable. Bei den nicht indizierten Spalte beobachten wir eine ähnliche Performance.

Fazit

Nachdem wir nun unsere Tests für die vier relevantesten DML-Operationen durchgeführt und ausgewertet haben, wird es Zeit für ein Fazit. Aus unseren Ergebnissen können wir entnehmen, dass die temporäre Tabelle im Allgemeinen eine bessere Performance liefert, als eine Tabellenvariable. Lediglich im Falle eines INSERT - und einigen DELETE - Bedingungen gewinnt die Tabellenvariable.

Besonders bei großen Datensätzen wird die höhere Performance der temporären Tabelle deutlich, da hier die Möglichkeit besteht Indizes zu verwenden, was der Tabellenvariable verwehrt bleibt. Bei einem kleinen Datensatz ist der Unterschied in der Performance fast zu vernachlässigen.

Abschließend kann gesagt werden, dass eine temporäre Tabelle dann die beste Option ist, wenn sehr viele Daten zu verarbeiten sind. Der beste Weg ist allerdings immer, beide Optionen zu testen und für sich selbst festzustellen, welches Objekt die beste Leistung für die gewünschte Anwendung bietet.