Opisz różne sposoby realizacji złożonych iteracyjnych obliczeń w SQL. Kiedy warto używać rekurencyjnych wspólnych wyrażeń tabelowych (CTE), a kiedy lepiej skorzystać z innych metod?

Odpowiedź.

W SQL istnieje kilka sposobów realizacji obliczeń iteracyjnych/rekursywnych:

• Rekurencyjne CTE (WITH RECURSIVE) — działają w przypadku zadań takich jak przeszukiwanie hierarchii (drzewa, grafy, łańcuchy odniesień).
• Kursory (CURSOR) — pozwalają przetwarzać dane wierszami, jednak są mniej wydajne dla dużych ilości niż operacje na zbiorach.
• Zwykłe wyrażenia oparte na zbiorach — są preferowane w większości przypadków (operują na zbiorach wierszy bez iteracji).

Kiedy używać rekurencyjnego CTE:

• Dla struktur hierarchicznych (kategorie produktów, struktury organizacyjne).
• Do wyszukiwania ścieżek/długości łańcuchów (na przykład, genealogia, grafy zależności).

Przykład:

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;

Jeśli rekurencyjny CTE nie rozwiązuje problemu lub dane są zbyt obszerne — stosuje się inne mechanizmy (na przykład zewnętrzne przetwarzanie, tymczasowe tabele lub specjalistyczne algorytmy poza SQL).

Pytanie z podchwytliwością.

Czy rekurencyjny CTE może prowadzić do niekończącego się wykonania? Jak tego uniknąć?

Odpowiedź: Tak, jeśli w grafie występują pętle (na przykład, parent_id ponownie odnosi się do potomka), rekurencyjny CTE może wpaść w pętlę. Większość systemów baz danych wspiera ograniczenie maksymalnej liczby poziomów rekurencji (MAXRECURSION dla MS SQL lub analogiczna opcja). Dobrą praktyką jest zawsze ograniczać głębokość rekurencji.

-- MS SQL Server
OPTION (MAXRECURSION 100)

Przykłady rzeczywistych błędów z powodu braku znajomości niuansów tematu.

---

Historia

W projekcie finansowym obliczano końcowy bilans według łańcuchów powiązanych kont (parent-child) za pomocą rekurencyjnego CTE. Jeden z użytkowników stworzył pętlę w strukturze (rodzic stał się swoim potomkiem). Zapytanie zaczęło się wykonywać w nieskończoność, obciążając serwer. Dodano kontrolę pętli i MAXRECURSION, problem został rozwiązany.

---

Historia

W systemie zarządzania zadaniami status zależnych zadań obliczano przetwarzaniem kursorowym. Po migracji na rekurencyjny CTE zapomniano uwzględnić optymalne indeksy według parent_id — wydajność spadła pięciokrotnie, ponieważ na każdym kroku miały miejsce kosztowne operacje join.

---

Historia

Zapytanie przeszukiwania drzewa kategorii za pomocą kursora działało poprawnie, ale zajmowało 7 minut dla 100 000 wierszy. Przepisano je na rekurencyjny CTE, który uważano za "wolny" — uzyskano ten sam wynik w 5 sekund dzięki optymalizacji mechanizmu pracy z zbiorami.