Beschrijf de verschillende manieren om complexe iteratieve berekeningen in SQL uit te voeren. Wanneer moet je gebruikmaken van recursieve Common Table Expressions (CTE) en wanneer is het beter om andere technieken toe te passen?

Antwoord.

In SQL zijn er verschillende manieren om iteratieve/recursieve berekeningen uit te voeren:

• Recursieve CTE (WITH RECURSIVE) — werken voor taken zoals het doorlopen van hiërarchieën (bomen, grafen, ketens van verwijzingen).
• Cursussen (CURSOR) — stellen je in staat om gegevens regel voor regel te verwerken, maar zijn minder efficiënt voor grote volumes dan set-gebaseerde operaties.
• Gewone set-gebaseerde expressies — zijn de voorkeur voor de meeste gevallen (werken met verzamelingen van rijen zonder iteraties).

Wanneer recursieve CTE gebruiken:

• Voor hiërarchische structuren (productcategorieën, organisatiestructuren).
• Voor het zoeken naar paden/lengtes van ketens (bijvoorbeeld, genealogie, afhankelijkheid grafen).

Voorbeeld:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS level
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.level + 1
  FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

Als een recursieve CTE het probleem niet oplost of de gegevens te omvangrijk zijn — worden andere mechanismen toegepast (bijvoorbeeld externe verwerking, tijdelijke tabellen of gespecialiseerde algoritmen buiten SQL).

Misleidende vraag.

Kan een recursieve CTE leiden tot oneindige uitvoering? Hoe kan dit worden voorkomen?

Antwoord: Ja, als er cycli in de grafiek zijn (bijvoorbeeld als parent_id opnieuw naar een kind verwijst), kan een recursieve CTE in een lus terechtkomen. De meeste databasesystemen ondersteunen een beperking van het maximale aantal niveaus van recursie (MAXRECURSION voor MS SQL of een vergelijkbare optie). Een goede praktijk is om altijd de diepte van de recursie te beperken.

-- MS SQL Server
OPTION (MAXRECURSION 100)

Voorbeelden van echte fouten door onwetendheid over de nuances van het onderwerp.

---

Verhaal

In een financieel project werd het eindbalans berekend via ketens van gekoppelde rekeningen (parent-child) met behulp van een recursieve CTE. Een van de gebruikers creëerde een cyclus in de structuur (de ouder werd zijn eigen kind). De aanvraag begon oneindig te draaien, wat de server belastte. We hebben cycli gecontroleerd en MAXRECURSION toegevoegd, het probleem was opgelost.

---

Verhaal

In het taakbeheersysteem werd de status van afhankelijke taken berekend met cursoreprocessing. Na de migratie naar een recursieve CTE vergaten we om optimale indexen op parent_id te voorzien — de prestaties daalden met 5 keer, aangezien er bij elke stap kostbare join-bewerkingen plaatsvonden.

---

Verhaal

De aanvraag voor het doorlopen van een boom van categorieën met behulp van een cursor werkte correct, maar nam 7 minuten in beslag voor 100.000 rijen. We herschreven het naar een recursieve CTE, die als "traag" werd beschouwd, — we kregen hetzelfde resultaat in 5 seconden dankzij optimalisatie door de set-gebaseerde engine.