SQL Server Performance von SELECT INTO vs INSERT INTO für temporäre Tabellen
Von: Jignesh Raiyani | Aktualisiert: 2017-01-04 | Kommentare (9) | Verwandt: Mehr > T-SQL
Kostenloses MSSQLTips Webinar: Development Best Practices for SQL Server
In diesem Webinar erfahren Sie mehr über Best Practices bei der Entwicklung von SQL Server. Andy Warren wird seine langjährige Erfahrung weitergeben, um einige Hinweise zu geben, was für ihn am besten funktioniert hat und wie Sie etwas von diesem Wissen nutzen können.
Problem
SQL Server beinhaltet SELECT…INTO und INSERT…INTO Code zum Einfügen von Daten in temporäre Tabellen. Können Sie einige Beispiele liefern und skizzieren, welche Option besser funktioniert?
Lösung
SQL Server enthält die beiden Optionen für temporäre Tabellen:
- Lokale temporäre Tabelle
- Globale temporäre Tabelle
Sie müssen das Präfix ‚#‘ für lokale temporäre Tabellen und ‚##‘ für globale temporäre Tabellen hinzufügen. Diese Objekte werden in der TempDB-Systemdatenbank erstellt. Temporäre Tabellen werden in der TempDB-Datenbank mit einem eindeutigen Objektnamen gespeichert und auf Verbindungs- oder Sitzungsebene erstellt. Diese Tabellen sind nur innerhalb der Sitzung sichtbar und zugänglich. Sie können die temporäre Tabelle mit dem Befehl DROP TABLE löschen oder die temporäre Tabelle wird automatisch gelöscht, wenn die Sitzung getrennt wird. Darüber hinaus führt SQL Server Statistiken für temporäre Tabellen.
Das Anlegen und Löschen von temporären Tabellen erfordert den Zugriff und die Änderung der TempDB-Zuordnungsseiten (IAM, SGAM und PES). Temporäre Tabellen werden im Vergleich zu permanenten plattenbasierten Tabellen im Pufferpool zwischengespeichert. Bei der Zwischenspeicherung von temporären Tabellen wird SQL Server die Tabelle nicht physisch löschen, sondern sie abschneiden und die IAM- und Datenseiten beibehalten. Wenn die Tabelle später erstellt wird, verwendet SQL Server die früheren Seiten wieder, was die Anzahl der erforderlichen Seitenänderungen reduziert.
Temporäre Tabellen können auf zwei Arten erstellt werden:
- CREATE TABLE
- SELECT INTO
Generell ist die Leistung beider Optionen für eine kleine Datenmenge ähnlich. Die Daten werden schnell in die temporäre Tabelle eingefügt, aber wenn die Datenmenge groß ist, kann es zu einer schlechten Abfrageleistung kommen. Das passiert normalerweise bei temporären Tabellen, wenn wir eine große Anzahl von Zeilen einfügen.
SQL Server INSERT INTO Beispiel
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 und Kompilierzeit:
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.
Bei einer großen Anzahl von kommagetrennten Werten als Eingabeparameter kann sich der Code beim Einfügen der Daten in das Temptable um einige Sekunden verzögern.
SQL Server SELECT INTO Beispiel
Als alternative Lösung können wir den SELECT…INTO-Befehl verwendet werden, der in der Regel besser abschneidet als der INSERT…SELECT-Befehl.
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- und Kompilierzeit:
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 mit einem größeren Datensatz
Die obigen Beispiele sind sehr klein, also lassen Sie uns das folgende Beispiel verwenden, um zu sehen, wie sich dies mit einer großen Datenmenge verhält. Dieser Test wird über eine Million Zeilen haben.
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- und Compile-Zeit:
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- und Kompilierzeit:
SQL Server Execution Times: CPU time = 1499 ms, elapsed time = 489 ms.(1727264 row(s) affected)
Ergebnisse und Analyse
Wie wir sehen können, war die SELECT…INTO deutlich schneller 489ms im Vergleich zu3241ms.
Der INSERT…INTO-Befehl verwendet Datenseiten wieder, die im Cache fürinsert/update/delete-Operationen angelegt werden. Der SELECT…INTO-Befehl erstellt neue Seiten für die Tabellenerstellung, ähnlich wie bei regulären Tabellen, und entfernt sie physisch, wenn die temporäre Tabelle gelöscht wird.
Nächste Schritte
- Behalten Sie diese Leistungsüberlegungen im Hinterkopf, wenn Sie Ihren Code entwickeln oder wenn Sie Code optimieren müssen, der nicht optimal funktioniert.
- Testen Sie weitere Szenarien in Ihrer Umgebung, um festzustellen, welche Codierungstechnik am besten funktioniert.
- Sind Sie immer offen für verschiedene Codierungstechniken, testen Sie jede Technik und lassen Sie die Leistung die Antwort diktieren.
Letzte Aktualisierung: 2017-01-04
Über den Autor
Jignesh Raiyani ist ein SQL Server Entwickler/DBA mit Erfahrung in Design und Entwicklung von T-SQL-Prozeduren und Performance-Tuning von Abfragen.Alle meine Tipps ansehen
- Mehr Tipps für Datenbankentwickler…