„Tabellenvariablen“ gegen „temporäre Tabellen“ (und weitere Ansätze) abwägen
Wir beschäftigen uns in diesem Blogbeitrag mit der Frage „T-SQL: Temporäre Tabellen gegen Tabellenvariablen, wo liegen die Vor- und Nachteile?“. Welche Möglichkeiten gibt es, stets ähnlichen und wiederholt auszuführenden Code auszulagern, der eine Tabelle als Rückgabewert hat?
In der Datenbankprogrammierung haben sich Sichten (Views) als geeignetes Datenbankobjekt etabliert, um den Umgang mit wiederholt auszuführenden Queries zu vereinfachen. Man lagert den Abfragecode in eine Sicht aus und kann dann beliebig auf diesen über den Namen der Sicht zugreifen. So vermeidet man redundante Programmierung und spart Codezeilen – was den Quellcode insgesamt deutlich lesbarer macht, die Fehlersuche unterstützt und die Zusammenarbeit im Team vereinfacht.
Das Problem…
Die Idee der Problemlösung über Views stößt aber schnell an Grenzen. Handelt es sich nicht stets um exakt dieselbe auszuführende T-SQL-Abfrage, muss eine Alternative her. In dieser soll ein Parameter zur Laufzeit übergeben und berücksichtigt werden. So gibt es exemplarisch die Anforderung, aus einer Menge von Rohdaten nur gefilterte Datensätze, beispielsweise für einen bestimmten Kunden, anzuzeigen. Aufgrund der Datenmenge im Gesamtbestand ist es keine gute Idee diese Auswahl erst im Ziel vorzunehmen. Die Filterung kann also nicht in der Komponente geschehen, die die Daten anfordert. Vielmehr muss die Reduzierung der Ergebnismenge schon vor der Datenauslieferung geschehen.
Ansatz 1: die (lokale/globale) temporäre Tabelle
Temporäre Tabellen werden nicht in der UserDB persistiert. Diese regulären Tabellendatenbankobjekte werden vom SQL Server automatisch in der TempDB verwaltet. Es gibt zwei Arten dieser Objekte, die sich bzgl. der Zugriffsrechte und des Gültigkeitsbereichs unterscheiden:
- lokale temporäre Tabellen (#temp)
- globale temporäre Tabellen (##temp)
Man erkennt die Art an den vorangestellten „#“ bzw. „##“. Beide werden wie normale Tabellen mit CREATE erstellt, mit DROP gelöscht und kennen ein ALTER als Strukturänderungsbefehl. Auch Indizierung funktioniert hier wie üblich.
Ansatz 2: die Tabellenvariable
Die Tabellenvariable (@temp) ist eine typische Variable, die mit DECLARE zu erstellen ist. Sie nimmt nicht nur einen einzelnen Wert aus einem bestimmten Gültigkeitsbereich auf (wie bspw. TINYINT), sondern kann den Inhalt einer ganzen Tabelle strukturiert aufnehmen. Daher wird sie wie eine Tabelle über die Angabe der Spaltennamen und Datentypen definiert.
Temporäre Tabellen gegen Tabellenvariablen – beide Ansätze im Vergleich:
(lokale/globale) temporäre Tabellen
Indizierung
Temporäre Tabellen sind reguläre Datenbankobjekte wie Standardtabellen. Sie werden nur in der TempDB verwaltet. Somit kann man sie beliebig indizieren.
notwendige Rekompilierung
Die Anlage, die Änderung und das Löschen einer temporärer Tabelle bedingen eine Schemaänderung und führen somit zu einer Rekompilierung.
Ablageort
Sie werden in der TempDB abgelegt.
Gültigkeitsbereich
- Lokale temporäre Tabellen sind nur für den Nutzer sichtbar, der sie angelegt hat. Sie werden spätestens automatisch gelöscht, wenn die aufrufende Verbindung geschlossen wird.
- Globale temporäre Tabellen sind für alle Benutzer nutzbar und werden spätestens mit dem Schließen der letzten Verbindung auf diese Tabelle (das muss nicht die initiale Verbindung sein!) gelöscht.
Transaktionen
Temporäre Tabellen sind (mit eingeschränktem Logging!) Bestandteil einer Transaktion. Somit bieten sie Sicherheit zum Preis erhöhter Laufzeit.
Verwendung in Funktionen
Eine Verwendung von temporären Tabellen innerhalb von Funktionen ist nicht möglich. Der Einsatz in Prozeduren ist problemlos.
Statistiken
Ähnlich wie reguläre Tabellen verfügen auch temporäre Tabellen über die gesamte Bandbreite an Statistiken. Diese Eigenschaft ist besonders für die Indizierung relevant.
Nutzung von DDL, DML
Man kann sowohl DDL- als auch DML-Operationen auf temporäre Tabellen anwenden! Eine Angabe von Fremdschlüsselbeziehungen zu/auf temporäre Tabellen ist nicht möglich.
Wertezuweisung
Temporäre Tabellen werden genauso befüllt wie man es mit normalen Tabellen macht (INSERT, UPDATE, MERGE, …). Sie müssen nicht zwingend vorher definiert (CREATE) werden, sondern können die Struktur von anderen Objekten übernehmen (INSERT INTO #temp SELECT * FROM original).
Locking
Temporäre Tabellen kennen Sperren und verhalten sich im Umgang mit diesen genauso wie ihre herkömmlichen Verwandten. Datenbankentwickler können dieses Verhalten mit Query-Hints beeinflussen.
Parameter in Stored Procedures
Die Verwendung von temporären Tabellen als Parameter ist nicht möglich. Man kann sie aber in JOINS als Datenbankobjekt nutzen.
Tabellenvariablen
Indizierung
Tabellenvariable sind nur eingeschränkt indizierbar. Clusterd Indizes (ohne Statistiken!) funktionieren in allen Versionen, mit SQL Server 2014 sind Non Clustered Indizes dazu gekommen.
notwendige Rekompilierung
Eine Rekompilierung ist bei Variablen nicht notwendig, da man Datenbankschemata nicht ändert.
Ablageort
Anders als oft behauptet, liegen Tabellenvariablen nicht nur im RAM – sie werden ebenso in der TempDB verwaltet.
Gültigkeitsbereich
Sie existieren nur innerhalb des Ausführungsscopes, in dem sie generiert wurden. Hierbei handelt es sich bspw. um ganze Prozeduren oder einzelne BEGIN-END-Blöcke. Wird der Scope verlassen, zerstören sich die Objekte mitsamt Inhalt selbst.
Transaktionen
Tabellenvariable nehmen nicht an Transaktionen teil. Somit bieten sie stark verringerte Laufzeit (u.a. kein Logging), bieten aber keinerlei Schutz.
Verwendung in Funktionen
Tabellenvariablen kann man sowohl in Funktionen als auch in Prozeduren einsetzen.
Statistiken
Anders als reguläre Tabellen verfügen Tabellenvariable über keinerlei Statistiken. Dies erklärt die Schwierigkeiten des Optimizers, Abfragen auf Tabellenvariable zu beschleunigen (fehlende Indizes).
Nutzung von DDL, DML
Es ist nicht möglich, die Struktur von bestehenden Tabellenvariablen zu ändern (bspw. per ALTER). Sie werden auch nicht wie bei DDL üblich per CREATE (DROP) erstellt oder gelöscht, sondern per DECLARE aufgebaut.
Wertezuweisung
Die Wert(e)zuweisung geschieht nicht per SET sondern bspw. über ein INSERT @temp EXEC proc.
Locking
Tabellenvariablen kennen ein Sperrverhalten. Aufgrund des eingeschränkten Gültigkeitsbereiches sind konkurrierende Zugriffe ohnehin ausgeschlossen.
Parameter in Stored Procedures
Die Nutzung von Tabellenvariablen ist in Stored Procedures sowohl als Eingangs- wie auch als Ausgangsparameter möglich. Als Beispiel sei hier das Projekt „BattleShips“ genannt…
Die Übersicht stellt die Vor- und Nachteile beider Lösungsansätze gegenüber. Es lohnt sich beide Varianten gegen eine entsprechend große Datenmenge laufen zu lassen. Schnell wird klar, dass temporäre Tabellen nicht ganz so flexibel sind. Sie spielen ihre Stärke aufgrund der Indizierung aber bei größeren Datenmengen aus und bringen eine spürbare Laufzeitoptimierung.
Weitere Lösungsideen „temporäre Tabellen gegen Tabellenvariablen“
Es gibt neben den beiden genannten Möglichkeiten noch andere Lösungen, mehr als nur einen Wert gleichzeitig als Rückgabe vom ausgelagertem Code zu erhalten. Hier ist vor allem die Tabellenwertfunktion zu nennen. Mein Projekt „ein Schachcomputer in reinem T-SQL“ macht ausgiebig von dieser Technik Gebrauch. Dabei handelt es sich um eine übliche Funktion, deren Rückgabewert aus einer Tabellenvariablen besteht. Allerdings unterliegt sie den typischen Einschränkungen von Funktionen. So ist es bspw. nicht möglich DDL (CREATE, ALTER, DROP, …) innerhalb von Funktionen zu nutzen. Auch DML (INSERT, UPDATE, DELETE, …) ist verboten.
Üblicher Anwendungsfall von temporären Objekten
Temporäre Tabellen eigenen sich hervorragend zur Speicherung von flüchtigen Zwischenergebnissen. Gerade mit dem INSERT-INTO-SELECT-Befehl sind sie schnell und ohne vorherige Definition zu erstellen und zu befüllen. Für kleinere Datenmengen, die durch eine Kette von Prozeduren/Funktionen zu schleusen ist, bieten sich hingegen Tabellenvariablen an.
