Décrivez différentes manières de réaliser des calculs itératifs complexes en SQL. Quand faut-il utiliser des Common Table Expressions (CTE) récursives et quand vaut-il mieux recourir à d'autres méthodes ?

Réponse.

En SQL, il existe plusieurs façons de réaliser des calculs itératifs/récursifs :

• CTE récursives (WITH RECURSIVE) — fonctionnent pour des tâches telles que le parcours d’hiérarchies (arbres, graphes, chaînes de liens).
• Curseurs (CURSOR) — permettent de traiter des données ligne par ligne, mais sont moins performants pour de grands volumes que les opérations basées sur des ensembles.
• Expressions basées sur des ensembles classiques — préférables dans la plupart des cas (opèrent sur des ensembles de lignes sans itérations).

Quand utiliser un CTE récursif :

• Pour des structures hiérarchiques (catégories de produits, structures organisationnelles).
• Pour rechercher des chemins/longueurs de chaînes (par exemple, généalogie, graphes de dépendance).

Exemple :

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;

Si le CTE récursif ne résout pas le problème ou si les données sont trop volumineuses, d'autres mécanismes sont appliqués (par exemple, traitement externe, tables temporaires ou algorithmes spécialisés en dehors de SQL).

Question piège.

Un CTE récursif peut-il entraîner une exécution infinie ? Comment cela peut-il être évité ?

Réponse : Oui, si le graphe contient des cycles (par exemple, parent_id se réfère à un descendant), le CTE récursif peut s’enliser. La plupart des SGBD prennent en charge une limite sur le nombre maximal de niveaux de récursivité (MAXRECURSION pour MS SQL ou option similaire). Une bonne pratique consiste à toujours limiter la profondeur de récursivité.

-- MS SQL Server
OPTION (MAXRECURSION 100)

Exemples d'erreurs réelles dues à une méconnaissance des subtilités du sujet.

---

Histoire

Dans un projet financier, le solde final était calculé à partir de chaînes de comptes liés (parent-enfant) via un CTE récursif. L'un des utilisateurs a créé un cycle dans la structure (le parent est devenu son propre descendant). La requête a commencé à s'exécuter indéfiniment, surchargeant le serveur. Une vérification des cycles et MAXRECURSION a été ajoutée, et le problème a été résolu.

---

Histoire

Dans un système de gestion des tâches, l'état des tâches dépendantes était calculé par un traitement de curseur. Après la migration vers un CTE récursif, ils ont oublié de prévoir des index optimaux sur parent_id — les performances ont chuté de 5 fois, car à chaque étape, des opérations de jointure coûteuses se produisaient.

---

Histoire

Une requête parcourant l'arbre des catégories à l'aide d'un curseur fonctionnait correctement, mais prenait 7 minutes pour 100 000 lignes. Ils l'ont réécrite en CTE récursif, considéré comme "lent", — ils ont obtenu le même résultat en 5 secondes grâce à l'optimisation du moteur de travail avec des ensembles.