clement13.net
Photo d’origine : Un rapporteur, une équerre et un compas posés sur d'anciens plans dessinés.
Description
Un rapporteur, une équerre et un compas posés sur d'anciens plans dessinés.
Source
Sergey Zolkin (szolkin) · page d’origine
Licence
CC0 Domaine public
Photo d’origine, non retouchée. L’arrière-plan en est une version stylisée par le site.

Principes en détail

Neuf idées pour comprendre pourquoi et comment penser en ensembles.

≈ 5 min de lectureLecture
En bref
  1. Une relation est un ensemble, sans ordre.
  2. Les opérations sont fermées et composables.
  3. On décrit, l'optimiseur décide.
  4. La boucle ligne à ligne coûte à chaque itération.
  5. Définir d'abord le grain.
  6. Attention : ordre, NULL, déterminisme.
Sur cette page
  1. 1. Le modèle relationnel
  2. 2. Un langage déclaratif et un optimiseur
  3. 3. Pourquoi la boucle ligne à ligne coûte cher
  4. 4. Penser en ensembles : une méthode
  5. 5. L'ordre n'existe pas (sauf si on le demande)
  6. 6. NULL et logique à trois valeurs
  7. 7. Déterminisme
  8. 8. Atomicité et transactions
  9. 9. Erreurs fréquentes
  10. Sources et vérification

1. Le modèle relationnel#

SQL repose sur le modèle relationnel proposé par E. F. Codd en 1970 : une relation est un ensemble de tuples (lignes) caractérisés par les mêmes attributs (colonnes). Les opérations de l'algèbre relationnelle ont une propriété décisive : elles sont fermées, c'est-à-dire qu'elles prennent des relations et rendent une relation. On peut donc les chaîner à volonté.

Opération relationnelleÉquivalent SQLRemarque
Sélection (σ)WHEREFiltre des lignes selon une propriété
Projection (π)liste de colonnes du SELECTAvec DISTINCT pour supprimer les doublons
Jointure (⋈)JOININNER, LEFT, RIGHT, FULL, CROSS
Union (∪)UNION / UNION ALLUNION ALL conserve les doublons
Intersection (∩)INTERSECT
Différence (−)EXCEPTUtile pour comparer deux résultats
Produit cartésien (×)CROSS JOINBase des tables de nombres et de calendriers
Agrégation (γ)GROUP BY, fonctions d'agrégatExtension de l'algèbre de base

SQL s'écarte du modèle sur trois points qu'il faut connaître : les tables peuvent contenir des doublons (multi-ensembles), la valeur NULL introduit une logique à trois valeurs, et l'ordre n'existe que si on le demande.

2. Un langage déclaratif et un optimiseur#

Une requête SQL décrit ce que l'on veut. Le moteur la transforme en plan d'exécution en plusieurs étapes : analyse syntaxique, résolution des objets, puis optimisation fondée sur le coût. L'optimiseur s'appuie sur les statistiques (distribution des valeurs) pour estimer le nombre de lignes à chaque étape, et choisit :

  • l'ordre des jointures et l'algorithme (boucles imbriquées, jointure par hachage, jointure par fusion) ;
  • le mode d'accès (recherche dans un index, parcours d'index ou de table) ;
  • la parallélisation et l'utilisation éventuelle du mode batch.

Plus l'instruction est complète, plus l'optimiseur a de latitude. Une boucle le prive de cette vue d'ensemble : il ne voit que des instructions isolées, une par itération.

3. Pourquoi la boucle ligne à ligne coûte cher#

Le traitement ligne à ligne est souvent appelé RBAR (Row By Agonizing Row, expression popularisée par Jeff Moden). Ses coûts se cumulent :

  • Surcoût par itération : récupération de la ligne, affectation de variables, lancement d'une instruction.
  • Journal de transactions : sans transaction explicite, chaque instruction est validée séparément ; le moteur attend l'écriture du journal à chaque ligne.
  • Verrous : acquis et relâchés à chaque itération, avec un risque d'attente prolongé sous charge.
  • Aucune vue d'ensemble pour l'optimiseur : pas de choix global d'algorithme, parallélisme limité.
  • Appels répétés si la boucle vit côté client : un aller-retour réseau par ligne.
Précision

Une boucle n'est pas toujours désastreuse : sur quelques dizaines de lignes, la différence est invisible. Le problème apparaît avec le volume, et surtout avec le doublement de volume qui double (ou pire) le temps de traitement.

4. Penser en ensembles : une méthode#

  1. Définir le grain du résultat : une ligne = quoi ? (une vente, un client par mois, une plage de dates).
  2. Lister les relations d'entrée : quelles tables fournissent les colonnes ?
  3. Exprimer les liens par des jointures, en choisissant INNER ou LEFT selon que l'absence de correspondance doit exclure ou conserver la ligne.
  4. Exprimer les filtres comme des propriétés (WHERE), pas comme des tests dans une boucle.
  5. Exprimer les calculs par regroupement (GROUP BY) ou par fenêtre (OVER).
  6. Vérifier le déterminisme, les NULL et les doublons avant de valider.

5. L'ordre n'existe pas (sauf si on le demande)#

  • Sans ORDER BY dans la requête finale, aucun ordre n'est garanti, même s'il semble stable en test.
  • TOP sans ORDER BY retourne des lignes arbitraires ; avec un ordre non unique, le départage est arbitraire aussi : ajouter une clé unique à l'ordre.
  • Dans OVER (ORDER BY …), l'ordre est logique : il définit la fenêtre de calcul, pas l'ordre d'affichage.

6. NULL et logique à trois valeurs#

Une comparaison avec NULL n'est ni vraie ni fausse : elle est inconnue, et WHERE ne retient que les lignes dont le prédicat est vrai.

  • COUNT(*) compte les lignes ; COUNT(colonne) ignore les NULL.
  • Une jointure sur une colonne contenant NULL ne rapproche jamais deux NULL.
  • NOT IN avec une sous-requête pouvant contenir NULL ne retourne aucune ligne.
-- Piège classique : NOT IN et NULL
DECLARE @A TABLE (v int NULL);
DECLARE @B TABLE (v int NULL);
INSERT INTO @A VALUES (1), (2), (3);
INSERT INTO @B VALUES (1), (NULL);

SELECT v FROM @A WHERE v NOT IN (SELECT v FROM @B);          -- 0 ligne ! (à cause du NULL)
SELECT v FROM @A AS a
WHERE NOT EXISTS (SELECT 1 FROM @B AS b WHERE b.v = a.v);    -- 2 et 3, comme attendu

Pour comparer deux valeurs en considérant deux NULL comme égaux :

-- Extraits de clause WHERE — comparaison « null-safe » (valable avant SQL Server 2022)
WHERE EXISTS (SELECT c.Valeur EXCEPT SELECT r.Valeur)   -- vrai si les deux valeurs diffèrent, NULL compris

-- SQL Server 2022 et Azure SQL : opérateur dédié
WHERE c.Valeur IS DISTINCT FROM r.Valeur

7. Déterminisme#

Un résultat doit dépendre des données, pas du plan. Les cas à risque : une mise à jour avec jointure où plusieurs lignes sources correspondent à une même ligne cible, un TOP sans ordre unique, un ROW_NUMBER avec des ex æquo. Le déterminisme est aussi une propriété d'objet que le moteur exploite : voir Déterminisme et objets déterministes.

-- Non déterministe : plusieurs lignes de B pour une ligne de A
UPDATE a
SET    a.Valeur = b.Valeur
FROM   dbo.A AS a
JOIN   dbo.B AS b ON b.Cle = a.Cle;        -- si B n'est pas unique sur Cle, la valeur retenue est arbitraire

-- Rendre le résultat déterministe : garantir l'unicité de la source
-- (clé primaire / contrainte UNIQUE sur B.Cle, ou agrégation / ROW_NUMBER préalable)

8. Atomicité et transactions#

Une instruction est atomique : elle réussit en entier ou échoue en entier. Une boucle d'instructions ne l'est pas : une erreur à la ligne 40 000 laisse 39 999 lignes déjà modifiées, sauf si l'on a ouvert une transaction explicite autour de la boucle (avec ses propres coûts de verrouillage et de journal).

9. Erreurs fréquentes#

  • Remplacer un curseur par une boucle WHILE qui fait la même chose : c'est le même traitement ligne à ligne.
  • Appeler une fonction scalaire dans une colonne de résultat sur de gros volumes (voir les faux ensemblistes).
  • Écrire un déclencheur en supposant qu'une seule ligne est insérée : une instruction peut en modifier des milliers.
  • Croire qu'un résultat est trié parce qu'il l'était en test.
  • Utiliser NOT IN sur une colonne nullable.
  • Multiplier les lignes par une jointure sur une clé non unique, puis « corriger » avec DISTINCT.

Sources et vérification#