×

Common Table Expressions (CTEs) Teil 1: Nicht-rekursive CTEs

Heutzutage werden ORMs (wie z.B. Hibernate unter Java oder NHibernate unter .NET) sehr von Entwicklern geschätzt. Vorbei ist die Zeit, umständlich mit Object-Arrays und Casts zu arbeiten – stattdessen wird nun einfach mit POJOs und bekannten Typen hantiert, wie man es von der jeweiligen Sprache gewohnt ist. Dazu ist man noch unabhängig von dem verwendeten Datenbanksystem.

Doch ORMs haben nicht nur Vorteile: Wenn man nicht aufpasst, was das jeweilige ORM im Hintergrund anstellt und welche SQL-Queries erstellt werden, kann man schnell in Performanceprobleme laufen. Zu den bekanntesten Problemen gehört, vor allem bei hierarchischen Daten, z.B. das n+1-Problem, in dem für n Objekte n+1 SQL-Queries ausgeführt werden.

Ein anderer Nachteil ist der eingeschränkte Funktionsumfang der Abfragesprache, die ein ORM mitbringt (z.B. HQL bei Hibernate). Zwar kann man praktischerweise eine Query unabhängig von der verwendeten Datenbank formulieren, allerdings sind dadurch datenbankspezifische Funktionalitäten aber auch Funktionalitäten, die von praktisch jeder Datenbank unterstützt werden, nicht verfügbar.

Eine dieser Funktionalitäten ist die sogenannte Common Table Expression oder kurz CTE. Eine CTE ist laut Microsoft „ein temporäres benanntes Resultset“. Vereinfacht gesagt kann man sich CTEs auch als eine Art View oder temporäre Tabelle vorstellen, die nur innerhalb einer Query gültig ist.

In ihrer nicht-rekursiven Form sind CTEs vor allem sinnvoll um komplexe Queries lesbarer zu machen, jedoch können CTEs auch Verweise auf sich selbst enthalten. Diese rekursiven Aufrufe können vor allem zum Traversieren von hierarchischen Datenmodellen (z.B. Baumstrukturen) sehr hilfreich sein.

Im ersten Teil dieses Blogartikels beschäftigen wir uns mit der nicht-rekursiven Variante der CTEs. Im zweiten Teil geht es dann um den Nutzen von rekursive CTEs.

Nicht-rekursive CTEs

Eine nicht-rekursive CTE hat vereinfacht folgende Syntax:

WITH  AS (
  SELECT…
)
SELECT/UPDATE/DELETE/INSERT…

Um die Syntax zu verdeutlichen, hier ein simples Beispiel: Nehmen wir an, wir hätten eine user-, eine role- und eine user_role-Tabelle, die Benutzern ein oder mehrere Rollen zuweist:

WITH rolesForUser AS (
  SELECT u.id AS user_id, u.login AS user_login, role.id AS role_id FROM user u
  INNER JOIN user_role ur ON ur.user_id = u.id
  INNER JOIN role r ON r.id = ur.role_id
)
SELECT user_id, user_login, COUNT(role_id)
FROM rolesForUser
GROUP BY user_id, user_login
ORDER BY user_login

Die rolesForUser-CTE kapselt die JOINs zwischen den drei Tabellen und liefert eine simple Sicht, welcher die Benutzer mit ihren Rollen ausgibt. Die resultierenden Reihen stehen dann in der nachfolgenden SELECT-Query zur Verfügung. Dabei wird die CTE aufgerufen, wie eine normale Tabelle, mit dem Unterschied, dass die CTE im selben Statement ad-hoc definiert wurde.

Man kann auch mehrere CTES innerhalb einer Query benutzen, wobei die einzelnen CTEs durch Komma getrennt sind und nachfolgende CTEs vorhergehende CTEs referenzieren können:

WITH  AS (
  SELECT...
),  AS (
  SELECT * FROM cte1... -- kann cte1 referenzieren
)
SELECT...

Dies hat den Vorteil, dass man komplexe Queries mit Hilfe von CTEs in mehrere logische Schritte unterteilen kann, ähnlich wie man Code einer Funktion zur besseren Lesbarkeit in mehrere Unterfunktionen aufteilt.

Queries lesbarer machen

Ein Anwendungsfall für CTEs sind unter anderem Queries in denen komplexe Ausdrücke mehrmals vorkommen. Ein Beispiel dafür ist die folgende Query. Angenommen eine Firma will ihren Mitarbeitern eine Prämie ausschütten in Abhängigkeit ihres Gehalts und der Firmenzugehörigkeit. Bei einer Zugehörigkeit von bis zu 10 Jahren soll es ein volles Gehalt geben, von 11 bis 20 Jahren 1.5 Gehälter und alles darüber 2 Gehälter. Als Ergebnis soll die Gesamtsumme der Prämien gruppiert nach dem Faktor für die Firmenzugehörigkeit ausgegeben werden: Das Ergebnis könnte dann folgendermaßen aussehen:

faktor summePraemien
1.0 25832
1.5 43952
2.0 92013

Standardquery

Eine mögliche Query um das gewünschte Resultat zu bekommen wäre folgende:

SELECT 
  CASE 
    WHEN ma.zugehoerigkeit BETWEEN 1 AND 10  THEN 1 
    WHEN ma.zugehoerigkeit BETWEEN 11 AND 20 THEN 1.5 
    ELSE 2 END AS faktor, 
  SUM(ma.gehalt) 
    * CASE 
      WHEN ma.zugehoerigkeit BETWEEN 1 AND 10  THEN 1 
      WHEN ma.zugehoerigkeit BETWEEN 11 AND 20 THEN 1.5 
      ELSE 2 END as summePraemien 
FROM Mitarbeiter ma 
GROUP BY CASE 
  WHEN ma.zugehoerigkeit BETWEEN 1 AND 10  THEN 1 
  WHEN ma.zugehoerigkeit BETWEEN 11 AND 20 THEN 1.5 
  ELSE 2 END 
ORDER BY faktor;

Hier sieht man deutlich wie der Teil zur Bestimmung des Faktors mehrmals verwendet werden muss, was die Query einerseits unleserlich macht, andererseits bei jeder Änderung des Faktors mehrere Stellen angepasst werden müssen. Dies kann schnell zu Flüchtigkeitsfehlern führen, wenn man eine Stelle vergisst oder falsch abändert. Lediglich bei ORDER BY wurde der Alias “faktor” verwendet, warum es also nicht bei den anderen Vorkommen auch verwenden? Das liegt an der Ausführungsreihenfolge eines SQL-Statements, die folgendermaßen ist:

  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • ORDER BY

So wird der SELECT-Teil fast am Schluss ausgeführt, weswegen der Alias erst im ORDER BY-Teil verwendet werden kann. Zumindest gilt dies für die meisten Datenbanksysteme, auch wenn z.B. MySQL oder Postgres da schlauer agieren und Aliase auch schon vorher zulassen.

Subselect

Eine Verbesserung bringt zum Beispiel die Benutzung eines subselects (oder auch inline-view oder derived table genannt):

SELECT 
  faktor.faktor, 
  SUM(ma.gehalt) 
        * faktor.faktor as summePraemien 
FROM Mitarbeiter ma 
INNER JOIN ( 
  SELECT 
    id, 
    CASE 
      WHEN zugehoerigkeit BETWEEN 1 AND 10  THEN 1 
      WHEN zugehoerigkeit BETWEEN 11 AND 20 THEN 1.5 
      ELSE 2 
    END as faktor 
  FROM Mitarbeiter) AS faktor ON faktor.id = ma.id 
GROUP BY faktor.faktor 
ORDER BY faktor.faktor;

Hier wird die Definition des Faktors in ein eigenes SELECT ausgelagert, auf welches man dann wiederum in der Query zugreifen kann. Dies verhindert zwar die Redundanz, aber gerade bei komplexeren Queries mit mehreren JOINS und subselects kann die Query wiederum schnell unübersichtlich werden.

CTE

Eine Alternative stellt die Benutzung einer CTE dar:

WITH mitarbeiterCTE AS ( 
  SELECT 
    gehalt, 
    CASE 
      WHEN zugehoerigkeit BETWEEN 1 AND 10  THEN 1 
      WHEN zugehoerigkeit BETWEEN 11 AND 20 THEN 1.5 
      ELSE 2 
    END as faktor 
  FROM ta_bestands_datum ma 
) 
SELECT SUM(gehalt) * faktor AS summePraemien, faktor FROM mitarbeiterCTE 
GROUP BY faktor 
ORDER BY faktor

Hier werden im ersten Schritt die benötigten Informationen, also Gehalt und Faktur für die Zugehörigkeit berechnet und im zweiten Schritt wird der Faktor dann auf das Gehalt angewendet. Durch die Anwendung von mehreren CTEs, die sich auch referenzieren können, kann man ein komplexes Statement in mehrere Zwischenschritte aufteilen, bevor am Ende das Endresultat ausgegeben wird, welches die Lesbarkeit und Wartbarkeit erhöhen kann.

Was ist jetzt „besser“?

Wie beschrieben stellen CTEs lediglich eine Alternative dar und fast immer gibt es mehr als eine Lösung, um auf das gewünschte Ergebnis zu kommen. Gerade bei großen Datenmengen spielt natürlich die Performanz eine große Rolle und da verschiedene Datenbanksysteme Queries verschieden optimieren, ist auch immer ein Ausprobieren der Alternativen und ein Blick auf den Ausführungsplan hilfreich. Zum Beispiel waren CTEs bei Postgres vor Version 12 in manchen Fällen ein sogenannter Optimization Fence, wodurch manche Optimierungen wie das Verschieben von Einschränkungen aus anderen Teilen der Query (z.B. “WHERE year > 2000”) in die CTE nicht möglich waren.

 

  In der nächsten Woche geht´s weiter mit Teil 2: Rekursiven CTEs – Wie sind sie aufgebaut und welchen Nutzen haben sie?

 

Von Robert Vollmann | 15.06.2020
Robert Vollmann

Softwareentwicklung