SQLShack (Deutsch)

Einführung

Haben Sie jemals eine komplexe Abfrage mit Common Table Expressions (CTEs) geschrieben, nur um von der Leistung enttäuscht zu sein? Haben Sie die CTE beschuldigt? Dieser Artikel befasst sich mit diesem Problem zu zeigen, dass es ein wenig tiefer als eine bestimmte Syntax Wahl und bietet einige Tipps, wie die Leistung zu verbessern.

CTEs Revisited

Common Table Expressions erschien zuerst in SQL Server 2005., In Kombination mit der WITH-Anweisung bieten sie eine Möglichkeit, eine komplizierte Hierarchie von Unterabfragen in eine leicht lesbare lineare Form umzuorganisieren. CTEs erlauben auch rekursives Verhalten, obwohl dies außerhalb des Rahmens dieses Artikels liegt. Zitat aus Books Online, ein CTE:

Gibt eine temporäre benannte Ergebnismenge an, die als Common Table Expression (CTE) bezeichnet wird. Dies wird aus einer einfachen Abfrage abgeleitet und im Ausführungsbereich einer einzelnen SELECT -, INSERT -, UPDATE-oder DELETE-Anweisung definiert. Diese Klausel kann auch in einer CREATE VIEW-Anweisung als Teil ihrer definierenden SELECT-Anweisung verwendet werden., Ein allgemeiner Tabellenausdruck kann Verweise auf sich selbst enthalten. Dies wird als rekursiver allgemeiner Tabellenausdruck bezeichnet.

Im Allgemeinen richten Sie eine Abfrage mit CTEs wie folgt ein:

Sie können eine beliebige Anzahl von CTEs haben und die endgültige Anweisung (die eine gültige DML sein kann) kann alle, einige oder keine davon verwenden., Angenommen, es gibt nur zwei, wie oben, kann dies mit Unterabfragen neu geschrieben werden:

1
2
3
4
5
6
7
8
9
10

SELECT …
AUS (
AUSWÄHLEN …
) cte1
JOIN (
AUSWÄHLEN …
) cte2
AUF cte1.,<Spalte beitreten> = cte2.<join column>

Es gibt keinen Unterschied zwischen der ersten Abfrage und der zweiten, soweit sie analysiert, kompiliert und ausgeführt wird. Die Ausführungspläne werden identisch sein. Der Unterschied liegt in der Lesbarkeit. Wenn Unterabfragen komplex sind, kann es einfacher sein, sie herauszuziehen und in CTEs zu setzen und sie dann in einer WITH Anweisung zu kombinieren.

Leistung?,

Vielleicht haben Sie einen CTE geschrieben oder debuggt, der langsam zu laufen scheint. Oder vielleicht haben Sie Beiträge in einem SQL Server-Forum gesehen, in denen Sie sich beschwert haben, dass einige CTE langsam ausgeführt werden. Was der Beschwerdeführer wirklich vorschlägt, ist, dass SQL Server beim Kompilieren einer aus CTEs erstellten Abfrage diese irgendwie anders als dieselbe Abfrage mit Unterabfragen ausführt und sie irgendwie schlechter abschneidet. Das ist einfach nicht der Fall., Wenn das Forumsplakat die Abfrage in (möglicherweise verschachtelte) Unterabfragen neu formatiert hätte und nicht die WITH…CTE-Struktur verwendet hätte, wären zweifellos dieselben Leistungsmerkmale beobachtet worden.

Warum läuft es so langsam! Gute Frage. Versuchen zu verstehen, warum, schauen wir uns ein einfaches Beispiel an:

Was macht das? Nach der Tabellenvariablendeklaration füllt es die Tabelle mit den ersten eine Million Zeilen aus dem kartesischen Produkt des sys.all_columns anzeigen., Abhängig davon, wie groß die Datenbank ist, gegen die Sie dies ausführen, können Tausende, Zehntausende und möglicherweise viele weitere Zeilen in dieser Tabelle vorhanden sein. Eine Million Zeilen im Ergebnis reichen für unsere Zwecke aus. Nach dem Auffüllen der Tabellenvariablen wird das Skript in einer WITH Anweisung mit einer CTE fortgesetzt, die nur Zeilen aus der Tabellenvariablen abruft. Sieht nicht so schlimm aus, oder? Bevor Sie es jedoch auf einem praktischen Produktionssystem ausführen, lesen Sie weiter.

Ein wenig Mathematik hilft Ihnen zu sehen, was auf dem Spiel steht., Wenn ich nur laufen:

1
2
3

SELECT COUNT(*)FROM sys.all_columns;

ich bekomme ein Ergebnis von 7364 auf meine system – und das ist nur LocalDB! Da dies mit dem kartesischen Produkt derselben Ansicht verbunden wird, könnte dies zu 7.364 Milliarden Zeilen führen. Ich lasse es alleine laufen (nicht Produktion!) System., Ich musste es stoppen, bevor es fertig werden konnte. Ich gebe zu, ein wenig ungeduldig zu sein!

Neugierig, was hinter dem Vorhang los ist, zeigte ich den Ausführungsplan an. Es sieht so aus:

Beachten Sie, dass ein verschachtelter Schleifenoperator ausgewählt wurde und dass die geschätzte Anzahl von Zeilen aus meiner Tabellenvariablen 1 ist! Obwohl ich es gerade mit 1 Million Zeilen geladen habe! Wie kann das sein? Die traurige Wahrheit ist, dass SQL Server keine Statistiken für Tabellenvariablen verwaltet, sodass zum Zeitpunkt der Kompilierung der Abfrage nicht bekannt ist, wie viele Zeilen wirklich vorhanden sind., Wenn ich nur eine Zeile hätte, wäre eine verschachtelte Schleife in Ordnung! So ist das in BOL definiert:

Der verschachtelte Schleifen-Join, auch verschachtelte Iteration genannt, verwendet eine Join-Eingabe als äußere Eingabetabelle (angezeigt als obere Eingabe im grafischen Ausführungsplan) und eine als innere (untere) Eingabetabelle. Die äußere Schleife verbraucht die äußere Eingabetabelle Zeile für Zeile. Die innere Schleife, die für jede äußere Zeile ausgeführt wird, sucht nach übereinstimmenden Zeilen in der inneren Eingabetabelle.

Für jede der Millionen Zeilen in meiner Tabellenvariablen führt SQL einen Scan der Objektkatalogansicht durch. Nicht! Gut!,

Mein zweiter Versuch ersetzt die Tabellenvariable durch eine temporäre Tabelle. Das Skript ist identisch, außer dass es beginnt mit:

1
2
3

TABELLE ERSTELLEN #t (id INT, Name SYSNAME);

Und ersetzt @t durch #t im Rest des Skripts., Diesmal sieht der Ausführungsplan folgendermaßen aus:

Das sieht besser aus! Zum einen ist die geschätzte Anzahl der Zeilen aus der temporären Tabelle korrekt. Zum anderen wählte der Complier eine Hash-Übereinstimmung – eine viel bessere Wahl für diesen Join als die Tabellenscans, die wir zuvor hatten.

Ich lasse diese Abfrage bis zum Abschluss laufen. Es lief etwas mehr als 3 Minuten auf meinem (zugegebenermaßen alten und langsamen!) und erzeugte 16,7 Milliarden Zeilen, bevor ein Fehler außerhalb des Speichers auftrat (das sind viele Zeilen!, Durch die Reduzierung der Anzahl der Zeilen in der temporären Tabelle auf 100.000 konnte sie in komfortablen neun Sekunden beendet werden.

All dies bedeutet, dass mit dem CTE-Konstrukt nichts falsch ist; etwas anderes muss los sein. Dass etwas anderes Statistik ist. Der SQL Server-Compiler verwendet Statistiken (z. B. Zeilenzahlen), um den Optimierer zu informieren. Statistiken werden jedoch nicht für Tabellenvariablen erstellt oder aktualisiert. Dies ist die „Aha!“moment! Das Ändern des Skripts, um eine temporäre Tabelle anstelle einer Tabellenvariablen zu verwenden, bedeutete, dass Statistiken verfügbar waren, um einen besseren Ausführungsplan zu wählen.,

Tiefer graben

Dass sich etwas anderes in der kleinen Phrase am Anfang des Zitats von BOL befindet:

Gibt eine temporäre benannte Ergebnismenge an

Warum ist das signifikant? Können Sie sich eine andere Art temporärer benannter Ergebnismenge vorstellen? Wie wäre es mit einer Tabellenvariablen! Wir haben bereits herausgefunden, dass Tabellenvariablen, die keine Statistiken haben, aufgrund falscher Zeilenzahlen mit Ausführungsplänen verwüsten können. Ein CTE enthält jedoch etwas Ähnliches in einer temporären Ergebnismenge. Beim Kompilieren eines komplexen CTE stehen möglicherweise nicht genügend Informationen zur Verfügung, um einen optimalen Plan abzuleiten., Wenn ich denke, dass das passiert, mache ich Folgendes:

  1. Teste jeden CTE von oben nach unten, um zu sehen, ob/wo Ausführungszeiten oder Zeilenzahlen explodieren., Dies ist in SSMS einfach durch Hinzufügen einer
    1
    2
    3

    SELECT * FROM &lt;CTE name&gt;

    Kurz vor der Hauptabfrage, die die CTEs zusammenzieht und das Skript bis zu diesem Punkt hervorhebt und ausführt.,

  2. Überprüfen Sie, ob der Täter korrekt mit den richtigen Prädikaten geschrieben ist.
  3. Stellen Sie sicher, dass die Prädikate indiziert sind, wenn möglich (mehr dazu später).
  4. Setzen Sie den Test von diesem CTE bis zum Ende fort.

Es gibt mindestens ein Problem, das mit diesem Prozess nicht behoben werden kann. Es ist nicht immer möglich sicherzustellen, dass alle Prädikate indiziert sind, insbesondere für abgeleitete Ergebnisse., In diesem Fall gibt es eine weitere Option, um den Engpass zu beheben:

Teilen Sie den CTE in eine temporäre Tabelle auf

Dies bedeutet, dass Ihre Abfrage neu strukturiert wird. Grundsätzlich nehmen Sie die obere Hälfte der Abfrage und schreiben die Ergebnisse in eine temporäre Tabelle, indizieren dann die Tabelle und setzen die Abfrage fort, beginnend mit der temporären Tabelle. Angenommen, ich habe:

Angenommen, das Problem liegt bei CTE10.,

Nachdem wir die Ergebnisse bisher in einer temporären Tabelle haben, indizieren Sie sie:

1
2
3
INDEX IX_#CTE9 AUF #CTE9 ERSTELLEN(col1, col2,…)

Jetzt beenden wir die Abfrage:

Beachten Sie, dass Sie den oben beschriebenen Prozess möglicherweise noch fortsetzen müssen, bis Sie Ihre Leistungsziele erreicht haben.,

Und noch etwas

Was auch immer Sie tun, gewöhnen Sie sich daran, Ausführungspläne zu lesen. Dort gibt es eine Fülle von Informationen und Hunderte, wenn nicht Tausende von großartigen Ressourcen, um sie zu verstehen. Als letzte Überprüfung der Vernunft klicken Sie jedoch mit der rechten Maustaste auf den Eintrag ganz links im grafischen Ausführungsplan. Das wird wahrscheinlich eine Auswahl sein, AKTUALISIEREN, EINFÜGEN oder LÖSCHEN. Wählen Sie nun im Kontextmenü Eigenschaften. Stellen Sie sicher, dass Sie dies ungefähr auf halbem Weg sehen:

Wenn Sie die Optimierungsstufe nicht VOLLSTÄNDIG sehen, ist dies ein Indikator dafür, dass Ihre Abfrage zu komplex ist., Erwägen Sie, es mit der oben beschriebenen temporären Tabellentechnik noch etwas aufzubrechen.

Allgemeine Tabellenausdrücke sind nur eine weitere Möglichkeit, Unterabfragen zu schreiben. Sie sind weder magisch noch behandelt SQL Server sie anders als normale verschachtelte Unterabfragen, mit Ausnahme ihrer rekursiven Cousins. CTEs können das Lesen und Warten von Code erleichtern, da Sie bei ordnungsgemäßer Verwendung Bedenken durch CTE trennen können. (Beachten Sie, wie ich mich in ein objektorientiertes Programmierprinzip eingeschlichen habe?)

Wenn sie schlecht abschneiden, geben Sie dem CTE jedoch keine Schuld., Graben Sie tiefer, um zu verstehen, was unter der Decke vor sich geht. Der Ausführungsplan ist der perfekte Weg, das zu tun.

  • Author
  • Recent Posts
Gerald Britton ist Senior SQL Server Solution Designer, Author, Software Developer, Teacher und Microsoft Datenplattform MVP. Er verfügt über langjährige Erfahrung in der IT-Branche in verschiedenen Funktionen.,
Gerald ist spezialisiert auf die Lösung von SQL Server-Abfrage Performance-Probleme vor allem, wie sie sich auf Business Intelligence-Lösungen beziehen. Er ist auch Co-Autor des eBooks „Erste Schritte mit Python“ und begeisterter Python-Entwickler, Lehrer und Pluralsight-Autor.
Sie können ihn auf LinkedIn finden, auf Twitter bei Twitter.,com/GeraldBritton oder @GeraldBritton und auf Pluralsight
Alle Beiträge von Gerald Britton anzeigen

Neueste Beiträge von Gerald Britton (alle anzeigen)
  • Snapshot Isolation in SQL Server – 5. August 2019
  • Verkleinern Ihrer Datenbank mit DBCC SHRINKFILE – 16. August 2018
  • Teilweise gespeicherte Prozeduren in SQL Server – 8. Juni 2018

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.