Auswahl zwischen Tabellenvariablen und temporären Tabellen (ST011, ST012)

Menschen können und tun viel über die relativen Vorzüge von Tabellenvariablen und temporären Tabellen streiten. Manchmal, wie beim Schreiben von Funktionen, haben Sie keine Wahl. Wenn Sie dies jedoch tun, werden Sie feststellen, dass beide ihre Verwendung haben, und es ist einfach, Beispiele zu finden, bei denen beide schneller sind. In diesem Artikel werde ich die wichtigsten Faktoren bei der Auswahl des einen oder anderen erläutern und einige einfache „Regeln“ demonstrieren, um die beste Leistung zu erzielen.

Angenommen, Sie befolgen die grundlegenden Regeln für das Engagement Wenn Sie mit relativ kleinen Datenmengen arbeiten, sollten Sie Tabellenvariablen als erste Wahl betrachten. Sie sind einfacher zu bearbeiten und lösen in den Routinen, in denen sie verwendet werden, weniger Neukompilierungen aus als temporäre Tabellen. Tabellenvariablen erfordern auch weniger Sperrressourcen, da sie für den Prozess und den Stapel, mit dem sie erstellt wurden, „privat“ sind. SQL Prompt implementiert diese Empfehlung als Code-Analyseregel. ST011 – Erwägen Sie die Verwendung einer Tabellenvariablen anstelle einer temporären Tabelle.

Wenn Sie eine komplexere Verarbeitung für temporäre Daten durchführen oder mehr als nur relativ kleine Mengen von verwenden müssen Daten in ihnen, dann sind lokale temporäre Tabellen wahrscheinlich die bessere Wahl. SQL Code Guard enthält eine Code-Analyseregel, basierend auf seiner Empfehlung ST012 – Erwägen Sie die Verwendung einer temporären Tabelle anstelle einer Tabellenvariablen, die jedoch derzeit nicht in der SQL-Eingabeaufforderung implementiert ist.

Vor- und Nachteile von Tabellenvariablen und temporären Tabellen

Tabellenvariablen neigen dazu, „schlechte Presse“ zu bekommen, da Abfragen, die sie verwenden, gelegentlich zu sehr ineffizienten Ausführungsplänen führen. Wenn Sie jedoch ein paar einfache Regeln befolgen, sind sie eine gute Wahl für Zwischen-Arbeitstabellen und für die Übergabe von Ergebnissen zwischen Routinen, bei denen die Datensätze klein sind und die erforderliche Verarbeitung relativ einfach ist.

Tabellenvariablen sind sehr einfach zu verwenden, vor allem, weil sie „wartungsfrei“ sind. Sie beziehen sich auf den Stapel oder die Routine, in der sie erstellt werden, und werden automatisch entfernt, sobald die Ausführung abgeschlossen ist, und verwenden sie daher innerhalb einer langlebigen Verbindung Wenn in einer gespeicherten Prozedur eine Tabellenvariable deklariert wird, ist sie für diese gespeicherte Prozedur lokal und kann in einer verschachtelten Prozedur nicht referenziert werden. Es gibt auch keine statistikbasierten Neukompilierungen für Tabellenvariablen und Sie können ALTER keine verwenden. Daher werden bei Routinen, die sie verwenden, tendenziell weniger Neukompilierungen durchgeführt als bei Routinen, die temporäre Tabellen verwenden benötigt weniger Platz im t Transaktionsprotokoll. Wenn sie in gespeicherten Prozeduren verwendet werden, gibt es unter Bedingungen hoher Parallelität weniger Konflikte mit Systemtabellen. Kurz gesagt, es ist einfacher, die Dinge ordentlich und ordentlich zu halten.

Wenn Sie mit relativ kleinen Datenmengen arbeiten, sind sie schneller als die vergleichbare temporäre Tabelle. Wenn jedoch die Anzahl der Zeilen über ungefähr 15.000 Zeilen hinaus zunimmt, jedoch je nach Kontext variiert, können Schwierigkeiten auftreten, hauptsächlich aufgrund der mangelnden Unterstützung für Statistiken. Selbst die Indizes, die PRIMARY KEY und UNIQUE Einschränkungen für Tabellenvariablen erzwingen, haben keine Statistiken . Daher verwendet der Optimierer eine fest codierte Schätzung von 1 Zeile, die von einer Tabellenvariablen zurückgegeben wird, und wählt daher tendenziell Operatoren aus, die für die Arbeit mit kleinen Datensätzen optimal sind (z. B. den Operator für verschachtelte Schleifen für Verknüpfungen). Je mehr Zeilen in der Tabellenvariablen enthalten sind, desto größer sind die Diskrepanzen zwischen Schätzung und Realität und desto ineffizienter werden die Planentscheidungen des Optimierers. Der daraus resultierende Plan ist manchmal furchtbar.

Der erfahrene Entwickler oder DBA wird nach solchen Problemen Ausschau halten und bereit sein, die OPTION (RECOMPILE) Abfragehinweis auf die Anweisung, die die Tabellenvariable verwendet. Wenn wir einen Stapel mit einer Tabellenvariablen senden, kompiliert der Optimierer zuerst den Stapel, an welchem Punkt die Tabellenvariable leer ist. Wenn der Stapel ausgeführt wird, führt der Hinweis dazu, dass nur diese einzelne Anweisung neu kompiliert wird. Zu diesem Zeitpunkt wird die Tabellenvariable ausgefüllt, und der Optimierer kann die tatsächliche Zeilenanzahl verwenden, um einen neuen Plan für diese Anweisung zu kompilieren. Manchmal, aber selten, hilft auch dies nicht weiter. Wenn Sie sich zu sehr auf diesen Hinweis verlassen, wird der Vorteil von Tabellenvariablen, weniger Neukompilierungen als temporäre Tabellen zu verursachen, in gewissem Maße zunichte gemacht.

Zweitens spielen bestimmte Indexbeschränkungen bei Tabellenvariablen bei der Behandlung eine größere Rolle große Datenmengen. Während Sie jetzt die Inline-Indexerstellungssyntax verwenden können, um nicht gruppierte Indizes für eine Tabellenvariable zu erstellen, gibt es einige Einschränkungen und es sind noch keine Statistiken zugeordnet.

Selbst bei relativ geringen Zeilenzahlen können Probleme mit der Abfrageleistung auftreten, wenn Sie versuchen, eine Abfrage auszuführen, bei der es sich um einen Join handelt, und Sie vergessen, eine PRIMARY zu definieren KEY oder UNIQUE Einschränkung für die Spalte, die Sie für den Join verwenden. Ohne die von ihnen bereitgestellten Metadaten kennt der Optimierer die logische Reihenfolge der Daten nicht oder weiß nicht, ob die Daten in der Verknüpfungsspalte doppelte Werte enthalten, und wählt wahrscheinlich ineffiziente Verknüpfungsvorgänge aus, was zu langsamen Abfragen führt. Wenn Sie mit einem Tabellenvariablen-Heap arbeiten, können Sie nur eine einfache Liste verwenden, die wahrscheinlich in einem einzigen Zug verarbeitet wird (Tabellenscan). Wenn Sie sowohl die Verwendung des Hinweises OPTION (RECOMPILE) für genaue Kardinalitätsschätzungen als auch einen Schlüssel in der Join-Spalte kombinieren, um dem Optimierer nützliche Informationen zu geben Metadaten: Bei kleineren Datensätzen können Sie häufig Abfragegeschwindigkeiten erzielen, die der Verwendung einer lokalen temporären Tabelle ähneln oder besser sind.

Sobald die Anzahl der Zeilen über die Komfortzone einer Tabellenvariablen hinaus ansteigt oder Sie komplexere Daten erstellen müssen Verarbeitung, dann wechseln Sie am besten zur Verwendung temporärer Tabellen. Hier stehen Ihnen alle Optionen für die Indizierung zur Verfügung, und der Optimierer hat den Luxus, Statistiken für jeden dieser Indizes zu verwenden. Der Nachteil ist natürlich, dass temporäre Tische mit höheren Wartungskosten verbunden sind. Sie müssen sicherstellen, dass Sie nach sich selbst aufräumen, um eine Überlastung der Tempdb zu vermeiden. Wenn Sie eine temporäre Tabelle ändern oder die darin enthaltenen Daten ändern, kann es zu Neukompilierungen der übergeordneten Routine kommen.

Temporäre Tabellen sind besser, wenn eine große Anzahl von Lösch- und Einfügungen erforderlich ist (Rowset-Freigabe) ). Dies gilt insbesondere dann, wenn die Daten vollständig aus der Tabelle entfernt werden müssen, da nur temporäre Tabellen das Abschneiden unterstützen. Die Kompromisse beim Entwurf von Tabellenvariablen, wie das Fehlen von Statistiken und Neukompilierungen, wirken ihnen entgegen, wenn die Daten flüchtig sind.

Wenn es sich lohnt, Tabellenvariablen zu verwenden

Wir Beginnen wir mit einem Beispiel, in dem eine Tabellenvariable ideal ist und zu einer besseren Leistung führt. Wir werden eine Liste der Mitarbeiter für Adventureworks erstellen, in welcher Abteilung sie arbeiten und in welchen Schichten sie arbeiten. Wir haben es mit einem kleinen Datensatz (291 Zeilen) zu tun.

Wir werden die Ergebnisse in eine zweite temporäre Tabelle einfügen, als würden wir das Ergebnis an den nächsten Stapel weitergeben. Listing 1 zeigt den Code.

Und hier ist ein typisches Ergebnis auf meinem langsamen Testcomputer:

Die Verwendung einer temporären Tabelle ist durchweg langsamer, obwohl einzelne Läufe sehr unterschiedlich sein können.

Die Probleme der Skalierung und des Vergessens, einen Schlüssel oder einen Hinweis anzugeben

Wie ist die Leistung, wenn wir zwei Tabellenvariablen verbinden? Probieren wir es aus. Für dieses Beispiel benötigen wir zwei einfache Tabellen, eine mit allen gebräuchlichen Wörtern in englischer Sprache (CommonWords) und die andere mit einer Liste aller Wörter in Bram Stokers Dracula (WordsInDracula). Der Download von TestTVsAndTTs enthält das Skript zum Erstellen dieser beiden Tabellen und zum Auffüllen jeder Tabelle aus der zugehörigen Textdatei. Es gibt 60.000 gebräuchliche Wörter, aber Bram Stoker verwendete nur 10.000 davon. Ersteres liegt weit außerhalb des Break-Even-Punkts, an dem temporäre Tabellen bevorzugt werden.

Wir verwenden vier einfache äußere Join-Abfragen und testen das Ergebnis auf NULL Werte, um die gebräuchlichen Wörter herauszufinden, die nicht in Dracula enthalten sind, gebräuchliche Wörter, die in Dracula vorkommen, Wörter in Dracula, die ungewöhnlich sind, und schließlich eine weitere Abfrage, um gebräuchliche Wörter in Dracula zu finden, die sich jedoch in die entgegengesetzte Richtung verbinden. Sie werden die Abfragen in Kürze sehen, wenn ich den Code für den Prüfstand zeige.

Nachfolgend sind die Ergebnisse der ersten Testläufe aufgeführt. Im ersten Durchlauf haben beide Tabellenvariablen Primärschlüssel und im zweiten sind sie beide Heaps, nur um zu sehen, ob ich die Probleme übertreibe, keinen Index in einer Tabellenvariablen zu deklarieren. Schließlich führen wir dieselben Abfragen mit temporären Tabellen aus. Alle Tests wurden zur Veranschaulichung absichtlich auf einem langsamen Entwicklungsserver ausgeführt. Mit einem Produktionsserver erhalten Sie sehr unterschiedliche Ergebnisse.

Die Ergebnisse zeigen, dass bei Abfragen der Tabellenvariablen das Risiko besteht, dass die Abfrage zehn Minuten statt 100 Millisekunden ausgeführt wird. Diese sind ein großartiges Beispiel für die schreckliche Leistung, die Sie erleben können, wenn Sie die Regeln nicht kennen. Selbst wenn wir Primärschlüssel verwenden, bedeutet die Anzahl der Zeilen, mit denen wir es zu tun haben, dass die Verwendung temporärer Tabellen jetzt doppelt so schnell ist.

Ich werde nicht auf die Details der Ausführungspläne eingehen, die dahinter stehen Leistungsmetriken, abgesehen von einigen allgemeinen Erklärungen der Hauptunterschiede. Für die temporären Tabellenabfragen wählt der Optimierer, der über umfassende Kenntnisse der Kardinalität und der Metadaten aus den Primärschlüsseleinschränkungen verfügt, einen effizienten Merge Join-Operator, um den Join-Vorgang auszuführen.Für die Tabellenvariable mit Primärschlüsseln kennt der Optimierer die Reihenfolge der Zeilen in der Join-Spalte und dass sie keine Duplikate enthalten. Er geht jedoch davon aus, dass es sich nur um eine Zeile handelt, und wählt stattdessen einen Join für verschachtelte Schleifen. Hier wird eine Tabelle gescannt und dann für jede zurückgegebene Zeile einzelne Suchvorgänge für die andere Tabelle ausgeführt. Dies wird umso weniger effizient, je größer die Datensätze sind, und ist besonders schlecht in den Fällen, in denen die Tabellenvariable CommonWords gescannt wird, da mehr als 60 KB nach Tabellenvariable. Der Nested Loops-Join erreicht für zwei zehnminütige Abfragen mithilfe von Tabellenvariablen-Heaps die maximale Ineffizienz, da er Tausende von Tabellenscans von CommonWords umfasst. Interessanterweise funktionieren die beiden „allgemeinen Wörter in Dracula“ -Abfragen viel besser, und dies liegt daran, dass der Optimierer für diese beiden stattdessen einen Hash-Match-Join gewählt hat.

Insgesamt scheinen die temporären Tabellen die beste Wahl zu sein , aber wir sind noch nicht fertig! Fügen wir den Abfragen OPTION (RECOMPILE) zu den Abfragen hinzu, die die Tabellenvariablen mit Primärschlüsseln verwenden, und Führen Sie die Tests für diese Abfragen und die ursprünglichen Abfragen mithilfe der temporären Tabellen erneut aus. Wir lassen die schlechten Heaps vorerst weg.

Wie Sie sehen, verschwindet der Leistungsvorteil der temporären Tabelle Bei korrekter Zeilenanzahl und geordneten Eingaben wählt der Optimierer den weitaus effizienteren Merge Join.

Was würde passieren, wenn Sie diesen armen Haufen die OPTION geben würden? (RECOMPILE) Hinweis auch? Siehe, die Geschichte ändert sich für sie, so dass alle drei Timings viel näher sind.

Interessanterweise fragen die beiden „gebräuchlichen Wörter in Dracula“ dies ab wurden schnell auch auf Haufen sind jetzt viel langsamer. Mit der richtigen Anzahl von Zeilen ausgestattet, ändert das Optimierungsprogramm seine Strategie. Da jedoch beim Definieren von Einschränkungen und Schlüsseln immer noch keine nützlichen Metadaten verfügbar sind, trifft es eine schlechte Wahl. Es scannt den CommonWords -Heap und versucht dann eine „Teilaggregation“, wobei geschätzt wird, dass er von 60 KB Zeilen auf einige Hundert aggregiert wird. Es weiß also nicht, dass es keine Duplikate gibt Tatsächlich aggregiert es überhaupt nicht, und die Aggregation und die anschließende Verknüpfung werden auf tempdb übertragen.

Der Prüfstand

Bitte beachten Sie, dass dies der Prüfstand in seiner endgültigen Form ist Dies zeigt ungefähr die gleiche Leistung für die drei verschiedenen Tabellentypen. Sie müssen die Hinweise OPTION (RECOMPILE) entfernen, um zum Original zurückzukehren.

Listing 2

Schlussfolgerungen

Die Verwendung von Tabellenvariablen ist nicht rücksichtslos. Sie bieten eine bessere Leistung, wenn sie für die Zwecke verwendet werden, für die sie bestimmt waren, und sie wischen selbst auf. Ab einem bestimmten Punkt werden die Kompromisse, die ihnen eine bessere Leistung bieten (keine Neukompilierungen auslösen, keine Statistiken bereitstellen, kein Rollback, keine Parallelität), zu ihrem Untergang.

Oft gibt der SQL Server-Experte weise Ratschläge dazu Die Größe des Ergebnisses, die Probleme für eine Tabellenvariable verursacht. Die Ergebnisse, die ich Ihnen in diesem Artikel gezeigt habe, deuten darauf hin, dass dies die Probleme zu stark vereinfacht. Es gibt zwei wichtige Faktoren: Wenn Sie ein Ergebnis von über 1000 Zeilen haben (und diese Zahl hängt vom Kontext ab), benötigen Sie eine PRIMARY KEY oder UNIQUE für alle Abfragen, die einer Tabellenvariablen beitreten. Ab einem bestimmten Punkt müssen Sie auch eine Neukompilierung auslösen, um einen anständigen Ausführungsplan zu erhalten, der seinen eigenen Overhead hat.

Selbst dann kann die Leistung stark leiden, insbesondere wenn Sie eine komplexere Verarbeitung durchführen , weil der Optimierer immer noch keinen Zugriff auf Statistiken hat und daher keine Kenntnis über die Selektivität eines Abfrageprädikats hat. In solchen Fällen müssen Sie zur Verwendung temporärer Tabellen wechseln.

Weiterführende Literatur