CTE rekursiv

Question

Hallo,

folgende Abfrage:

use TSQL2012
go
select firstname, lastname, empid, mgrid
from HR.Employees

bringt mit sich folgendes  Ergebnis:

.

wenn man hingegen folgendes ausführt:

WITH EmpsCTE AS
(
  SELECT empid, mgrid, firstname, lastname, 0 AS distance
  FROM HR.Employees
  WHERE empid = 9

  UNION ALL

  SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
  FROM EmpsCTE AS S
    JOIN HR.Employees AS M
      ON S.mgrid = M.empid
)

Ich bin Programmieranfänger, deswegen wäre es Klasse, wenn mir jemand erklären könnte, wie dieses Ergebnis zustande kommt.

---

Arek

Answer 1

Hallo Arek,

deine Frage ist mir nicht wirklich klar, was verstehst Du denn nicht?

ist dein Problem, warum bei der Zweiten abfrage so wenig Elemente raus kommen oder was meinst Du?

Viele Grüße

BlackCoin 

Answer 2

Hallo Arek,

mit Deiner ersten Abfrage fragst Du lediglich die Tabelle selbst in einer simplen Form ab während Du mit der zweiten Abfrage eine "hierarchische Abfrage" baust, die Elements von sich selbst erneut aufruft (rekursiv).

Mit dem ersten Teil der CTE fragst Du den "Ankerpunkt" ab, ab dem begonnen werden soll (In deinem Fall der Mitarbeiter mit der Id = 9). Der zweite Teil verwendet dieses Ergebnis, um damit die nächste Hierarchie (Alle Manager von 9) zu finden. Damit baust Du die n-te Hierarchie auf.

Da Du die Abfrage mit sich selbst aufrufst, wird sie solange ausgeführt, bis kein höheres Level mehr gefunden wird. Erst dann wird die Abfrage beendet.

CTE wurden mit SQL 2005 eingeführt und haben hierarchische Abfragen massiv vereinfacht. Früher musste man entweder mit temporären Tabellen arbeiten oder aber man mußte bereits im Vorfeld wissen, wie viele Hierarchien ermittelt werden. Nehmen wir Dein Beispiel, wäre das "früher" wie folgt zu lösen gewesen:

SELECT *
FROM
(
	SELECT empid, firstname, lastname, mgr_id, 0 AS Distance
	FROM dbo.Employees WHERE empid = 9

	UNION

	SELECT empid, firstname, lastname, mgr_id, 1 AS Distance
	FROM dbo.Employees
	WHERE empid IN (SELECT mgr_id FROM dbo.Employees WHERE empid = 9)

	UNION

	SELECT empid, firstname, lastname, mgr_id, 2 AS Distance
	FROM dbo.Employees
	WHERE empid IN
	(
		SELECT mgr_id
		FROM dbo.Employees
		WHERE empid IN (SELECT mgr_id FROM dbo.Employees WHERE empid = 9)
	)

	UNION

	SELECT empid, firstname, lastname, mgr_id, 3 AS Distance
	FROM dbo.Employees
	WHERE empid IN
	(
		SELECT mgr_id
		FROM dbo.Employees
		WHERE empid IN
		(
			SELECT mgr_id
			FROM dbo.Employees
			WHERE empid IN (SELECT mgr_id FROM dbo.Employees WHERE empid = 9)
		)
	)
) AS result
ORDER BY Distance;

Wie Du sicherlich bereits jetzt erahnen kannst, ist diese Art der Abfrage eher:

• unübersichtlich
• unflexibel

Aus diesem Grund wurden damals die CTE eingeführt. CTE wurden insbesondere für die Vereinfachung von Abfragen entwickelt; in diesem speziellen Fall ist die Performance eher zweitrangig:

Leider sind die Abmessungen für Bilder hier beschränkt :(. Obwohl der Auführungsplan vermittelt, dass die "komplexe" Abfrage das Nachsehen hat, stimmt das nicht. Die Prozentangaben sind "estimated" costs. Tatsächlich aber ist die UNION-Variante performanter. Auf meinem Laptop habe ich für das Beispiel wie folgt gemessen:

• 32ms für UNION-Abfrage
• 138 ms für CTE

Das soll aber nicht heißen, dass CTE per se langsamer sind - spätestens, wenn Du eine weitere Hierarchieebene einfügen möchstest, wird dieses Verhältnis kippen. Das habe ich aber jetzt nicht mehr ausprobiert ;)

---

MCM - SQL Server 2008
MCSE - SQL Server 2012
db Berater GmbH
SQL Server Blog (german only)

Answer 3

Hallo,

danke für deine Antwort. Ich verstehe nicht, was es auf sich hat mit der Rekursion. Woher kommt das Ergebnis zustande? Zuerst wird in der Where Klausel nach der Nummer 9 gefiltert und dann wird das Ergebnis mit allen Mitarbeitern gejoint? Ich bräuchte eine Erklärung, was da genau passiert.

Danke.

---

Arek

Answer 4

Hallo Arek,
wenn Dir die Antwort von Uwe nicht ausführlich genug ist, dann schau mal in die Online-Doku:
http://msdn.microsoft.com/de-de/library/ms186243.aspx
Rekursive Abfragen mithilfe von allgemeinen Tabellenausdrücken

Hieraus noch einmal herauskopiert die Semantik der rekursiven Ausführung:
1. Aufteilen des CTE-Ausdrucks in Ankerelemente und rekursive Elemente.
2. Ausführen der Ankerelemente zum Erzeugen des ersten Aufrufs oder Basisresultsets (T0).
(Das ist die Zeile mit empid = 9)
3. Ausführen der rekursiven Elemente mit Ti als Eingabe und Ti+1 als Ausgabe.
(Das ist der zweite Teil mit S.mgrid = M.empid)
4. Wiederholen von Schritt 3, bis ein leeres Set zurückgegeben wird.
5. Rückgabe des Resultsets. Das ist ein UNION ALL von T0 bis Tn.

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu