Schlagwort: Datenbank

Rekursive SQL-Abfragen

Am Ende Jahres 2009 stand ich vor der Problematik, dass meine Applikation mit mehr als 3000 Benutzern und mehreren Terra-Byte an Daten immer mehr an Performance verlor.
Besonders die Administratoren bedanken sich, bei Aufrufzeiten von über einer Minute für jeden Seitenwechsel, beim System.
So begann ich erstmal die  in Java geschriebenen objektorientierten, rekursiven Methoden Schritt für Schritt aufzulösen.
Der Ist-Wert vor dem Umbau lag bei bis zu 870 SQL-Anfragen pro Seitenaufbau, nach dem Umbau lang er konstant bei 5 Aufrufen und einer Performanceoptimierung von circa 700%.
Dabei kam mit besonders die Verwendung von rekursiven SQL-Abfragen auf der Datenbank entgegen, um die in Ordner gruppierten Objekte hierachisch auflösen zu können.
Voraussetzung:

DIRECTORY-Table: DIRECTORY_ID, PARENT_ID
LEVEL eine Integer oder Long Variable zur Beendigung des SQL im Fehlerfall infinite LOOP

Rekursive SQL-Abfrage:

with r (groupid,groupparentid, level) AS ( — feste Syntax:  erstes Ergebnis wird in der temporären Tabelle r vorgehalten wird
select dto.DIRECTORY_ID, dto.PARENT_ID, 1 AS level from DIRECTORY-Table dto where PARENT_ID IN ( ‚1‘,’2′,’3′)  — mit der ersten SQL-Anfrage, die die Tabelle r füllt
UNION ALL — feste SQL Ausdruck für die Rekursion
select dt. DIRECTORY_ID,dt.PARENT_ID,r.level +1 from DIRECTORY-Table dt,r where dt.PARENT_ID = r.groupid and r.groupid IS NOT NULL and r.level <= LEVEL  — zweite SQL-Abfrage mit der Rekursivebedingung und der Verwendung des Ergebnis des ersten SQL-Statements
)
select * from DIRECTORY-Table g,r where g.GROUP_ID = r.groupid) — letzendliches SELECT-Statement für das zurückzugebende Ergebnis
Anmerkung:
Die Variable LEVEL mit der Abbrechbedingung im zweiten SQL-Statement ist nicht notwendig, aber dringend zu empfehlen. Sollten mal Datensätze auf sich gegenseitig verweisen und so eine Endlosschleife entstehen bricht die Rekursion in der definierten Tiefe von LEVEL ab, da ja keine normale Abbruchbedingung mit leerer Ergebnismenge des 2. SQL-Statements zustande kommt. Sollte diese nicht definiert werden, kommt es auf die Datenbank darauf an, wie es mit der Infinit-Loop umgeht.

[Quellen]
http://www.mayeruli.de/db2/rekursives-sql.html
http://www.thomas.teufl.eu/blog/2007/09/27/t-sql-rekursive-abfragen/
http://www.orafaq.com/node/1879