WITH-Klausel: Performance Auswirkungen


Die nicht-rekursive With-Klausel, Views, und Unterabfragen in der From-Klausel („derived tables“) bieten sehr ähnliche Funktionalität. Der größte Unterschied ist die Sichtbarkeit. Daher legt das Prinzip der geringsten Überraschung nahe, das sich eine Änderung der Form – z. B. von einer Unterabfrage in eine With-Abfrage – keine Nebenwirkungen auf die Performance haben sollte.

Die folgenden Abschnitte skizzieren die wichtigsten Optimierungen, die mit der With-Klausel zusammenspielen. Abschließend zeigt eine Übersicht, welche Datenbanken diese Optimierungen auf With-Klauseln, Views, und Unterabfragen anwenden.

Predicate Pushdown – Filter runterreichen

Predicate Pushdown ist eine Optimierung, um Filterbedingungen (Prädikate) so früh wie möglich anzuwenden. Im Idealfall werden nicht benötigte Zeilen erst garnicht geladen.

Das folgende Beispiel zeigt Predicate Pushdown bei einer Unterabfrage in der From-Klausel:

SELECT c1, c2, ...
  FROM (SELECT c1, c2, ...
          FROM base_table
       ) derived_table
 WHERE c1 = ?

Wenn die Unterabfrage isoliert ausgeführt wird, liefert sie alle Zeilen der Tabelle base_table. Selbst wenn es einen Index auf c1 gibt, kann er nicht benutzt werden, weil der Filter auf c1 für die Unterabfrage so nicht sichtbar ist.

Durch Predicate Pushdown wird der Filter and die Unterabfrage runtergereicht, sodass die Abfrage de-facto folgendermaßen aussieht:

SELECT c1, c2, ...
  FROM (SELECT c1, c2, ...
          FROM base_table
         WHERE c1 = ?
       ) derived_table

Bei diesem Beispiel kann man natürlich die Meinung vertreten, dass die Where-Klausel ohnehin in die Unterabfrage gehört. Dieses Argument kann jedoch nicht geltend gemacht werden, wenn anstatt einer Unterabfrage auf einen View zugegriffen wird.

Predicate pushdown unterliegt einigen logischen Einschränkungen. So ist es zum Beispiel nicht möglich, Join-Prädikate weiter als bis zum ersten Join den sie betreffen runterzureichen. Andererseits können Prädikate auch durch group by und Window-Funktionen gereicht werden, wenn sie nur den Gruppen-, oder Partitionierungsschlüssel betreffen.

Predicate pushdown ist besonders wichtig, um bessere Indizierung zu ermöglichen.

Projection Pushdown – Spalten runterreichen

Projection Pushdown – das runterreichen von Spalten – verwirft ungenuzte Spalten so früh wie möglich. Im Idealfall werden sie erst garnicht geladen.

Das folgende Beispiel zeigt Projection Pushdown beim Zugriff auf einen View:

CREATE VIEW view_name AS
SELECT *
  FROM base_table
 WHERE deleted = 0
SELECT count(*)
  FROM view_name

Beachte, dass count(*) keine Tabellenspalten benötigt.0 Wenn die Datenbank die Information, welche Spalten benötigt werden, runterreicht, kann sie es vermeiden nicht benötigte Spalten zu laden.

Im oberen Beispiel ist das Projection Pushdown besonders wichtig, wenn es einen Index auf der Spalte deleted gibt. In diesem Fall kann die Abfrage alleine durch einen Zugriff auf den Index ausgeführt werden (der sogenannte Index-Only-Scan). Der Zugriff auf die Tabelle selbst kann entfallen. Dadurch kann die Abfrage um Faktoren schneller werden. Mehr über den Index-Only-Scan gibt es auf Use The Index, Luke!

Wenn du diese Seite magst, magst du vielleicht auch …

… meine Newsletter bestellen, gratis Sticker erhalten, mein Buch kaufen oder an einer Schulung teilnehmen.

Sortierungen eliminieren

Durch die Eliminierung von Sortierungen werden redundante Sortierungen vermieden. Redudante Soriterungen können zum Beispiel durch mehrere Order By-Klauseln entstehen:

WITH cte
  AS (SELECT id
        FROM feature_with
       ORDER BY id
     )
SELECT id
  FROM cte
 ORDER BY id

Natürlich ist es unnötig, das Ergebnis zweimal zu sortieren. Selbst wenn die beiden Order By-Klauseln unterschiedlich wären, legt nur die äußerste Order By-Klausel die Reihenfolge im Endergebnis fest. Abgesehen von einigen Ausnahmen1 können alle ausser der äußersten Order By-Klausel eliminiert werden.

Redundante Sortierungen sind weniger offensichtlich, wenn sie durch die Kombination von Order By, Ggroup By (manchmal Sortier-basiert), over (partition by … order by…) und Join-Operationen (Sort-Merge) enstehen. Dazu kommt, dass Indizes die Zeilen in einer nützlichen Reihenfolge liefern könnten und eine Sortierung hinfällig wird.

Im folgenden Beispiel könnte eine Datenbank die Zeilen entsprechend der Partition By und Order By-Klauseln sortieren – also nach (c1, c2) –, um die Window-Funktion rRow_number() aufzulösen.2 In diesem Fall liefert die Abfrage in der With-Klausel die Zeilen bereits entsprechend der äußeren Order By-Klausel. Die äußere Sortierung kann also eliminiert werden.

WITH cte
  AS (SELECT c1
           , c2
           , ROW_NUMBER()
             OVER(PARTITION BY c1 ORDER BY c2) rn
        FROM table_name
     )
SELECT c1, rn
  FROM cte
 ORDER BY c1, c2

Beachte, dass die äußere Order By-Klausel dennoch notwenig ist. Die Over-Klausel legt lediglich die Reihenfolge zur Berechnung der Window-Funktion fest. Wenn die Datenbank die Window-Funktion anders Berechnet – z. B. die Partitionen auf paralelle Prozesse aufteilt – kann die äußere Sortierung nicht eliminiert werden. Lässt man die äußere Oorder By-Klausel weg, kommt das Ergebnis in diesem Fall in undefinierter Reihefolge.

Produktvergleich

Nicht alle getesteten Datenbanken optimieren With-Klauseln so gut (oder schlecht), wie sie Views und Unterabfragen optimieren.

Db2 (LUW) 12.1.2dMariaDB 12.0.2adMySQL 9.3.0dOracle DB 23.9dPostgreSQL 17dSQL Server 2022bccSQLite 3.50.0adFilter runterreichen: withSpalten runterreichen: withSortierung eliminieren: withFilter runterreichen: derivedSpalten runterreichen: derivedSortierung eliminieren: derivedFilter runterreichen: viewSpalten runterreichen: viewSortierung eliminieren: view
  1. Nicht mit Window-Funktionen
  2. Nicht mit Window-Funktionen • Wenn man in der Unterabfrage top verwendet
  3. Wenn man in der Unterabfrage top verwendet
  4. Wenn man eine Unterabfrage im View verwendet, damit man dort Order By verwenden kann

20 Jahre SQL-Evolution kann man nicht an einem Tag nachholen. Abonniere den Newsletter via E-Mail, Bluesky oder RSS, um sukzessive aufzuholen und modern-sql.com am Radar zu behalten.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Fußnoten

  1. ISO/IEC 9075-2:2023 §10.9 GR 5: Das Ergebnis von count(*) ist als die Zeilenzahl definiert – unabhängig etwaiger null-Werte.

  2. Z. B. wenn es eine fetch first-Klausel gibt; wenn das order by in einer over oder within group-Klausel steht und der gleichen.

  3. Die gängige Art Window-Funktionen auszuführen ist, zuerst nach dem kombinierten Schlüssel aus Partition By und Oorder By Ausdrücken zu sortieren. Die Auflösung der Window-Funktion findet dann auf Basis der sortierten Daten statt.

Neuigkeiten Abonnieren

Markus Winands MailinglistenMarkus Winands RSS-FeedMarkus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf TwitterMarkus Winand auf Bluesky
Copyright 2015-2025 Markus Winand. Alle Rechte vorbehalten.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO