Descrivi i vari modi per implementare calcoli iterativi complessi in SQL. Quando è opportuno utilizzare Common Table Expressions (CTE) ricorsive e quando è meglio ricorrere ad altre tecniche?

Risposta.

In SQL esistono diversi modi per realizzare calcoli iterativi/ricorsivi:

• CTE ricorsive (WITH RECURSIVE) — utilizzati per attività come l'attraversamento di gerarchie (alberi, grafi, catene di riferimenti).
• Cursori (CURSOR) — consentono di elaborare i dati riga per riga, tuttavia sono meno performanti per grandi volumi rispetto alle operazioni basate su set.
• Espressioni basate su set normali — preferibili nella maggior parte dei casi (operano su insiemi di righe senza iterazioni).

Quando utilizzare un CTE ricorsivo:

• Per strutture gerarchiche (categorie di prodotto, strutture organizzative).
• Per trovare percorsi/lunghezze di catene (ad esempio, genealogia, grafi di dipendenza).

Esempio:

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;

Se il CTE ricorsivo non risolve il problema o se i dati sono troppo voluminosi, si applicano altri meccanismi (ad esempio, elaborazione esterna, tabelle temporanee o algoritmi specializzati al di fuori di SQL).

Domanda insidiosa.

Può un CTE ricorsivo portare a un'esecuzione infinita? Come prevenirlo?

Risposta: Sì, se nel grafo ci sono cicli (ad esempio, parent_id si riferisce di nuovo a un discendente), il CTE ricorsivo può entrare in un ciclo. La maggior parte dei DBMS supporta la limitazione del numero massimo di livelli di ricorsione (MAXRECURSION per MS SQL o un'opzione simile). È buona pratica limitare sempre la profondità della ricorsione.

-- MS SQL Server
OPTION (MAXRECURSION 100)

Esempi di reali errori dovuti alla mancanza di conoscenza delle sfumature dell'argomento.

---

Storia

In un progetto finanziario calcolavamo il saldo finale tramite catene di conti correlati (parent-child) tramite un CTE ricorsivo. Uno degli utenti creò un ciclo nella struttura (un genitore divenne il proprio discendente). La query ha cominciato a essere eseguita all'infinito, sovraccaricando il server. Aggiungemmo un controllo sui cicli e MAXRECURSION, il problema si risolse.

---

Storia

Nel sistema di gestione delle attività, lo stato delle attività dipendenti veniva calcolato tramite elaborazione a cursore. Dopo la migrazione a CTE ricorsivi non si era pensato a prevedere indici ottimali su parent_id — le prestazioni sono diminuite di 5 volte, poiché a ogni passaggio si verificavano costly join.

---

Storia

La query per attraversare l'albero delle categorie tramite cursore funzionava correttamente, ma impiegava 7 minuti per 100.000 righe. Riscrivemmo utilizzando un CTE ricorsivo, considerato "lento", — ottimizzammo e ottenemmo lo stesso risultato in 5 secondi grazie all'ottimizzazione del motore di lavoro con insiemi.