clement13.net
Photo d’origine : Panneau « Speed limit 5 » sur le muret d'un parking en hauteur : une limite fixée, mais qui a sa raison d'être.
Description
Panneau « Speed limit 5 » sur le muret d'un parking en hauteur : une limite fixée, mais qui a sa raison d'être.
Source
MarkBuckawicki · 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.

Quand le procédural est légitime

Le discernement avant le dogme : où l'ensembliste s'arrête, et ce qui lui ressemble sans en être.

≈ 3 min de lectureLecture
En bref
  • Légitime : boucle sur des objets, lots, effets externes, logique séquentielle irréductible.
  • À éviter : fonctions scalaires, TVF multi-instructions, sous-requêtes corrélées sur gros volumes, déclencheurs ligne à ligne.
  • Test : « sur quoi la boucle itère-t-elle ? »
Sur cette page
  1. Quand le procédural est légitime
  2. Les faux ensemblistes
  3. Garde-fous
  4. Sources et vérification

L'ensembliste n'est pas une religion : c'est un outil. Cette page marque ses limites, pour éviter l'erreur inverse (forcer une solution ensembliste là où elle n'a pas de sens) et pour reconnaître les faux ensemblistes.

Quand le procédural est légitime#

  • Boucle sur des objets, pas sur des données : reconstruire les index de chaque table, appliquer une opération à chaque base, parcourir une liste de paramètres. Chaque itération pilote un traitement ensembliste.
  • Traitement par lots : découper une grosse suppression ou mise à jour en lots pour contrôler le journal, les verrous et la durée.
  • Effets externes : envoi de message, appel d'un service, écriture de fichier : le moteur ne sait pas les grouper.
  • Logique séquentielle irréductible : certains calculs où chaque étape dépend d'un état calculé par la précédente, sans formulation fenêtrée ou récursive raisonnable.
  • Très petits volumes : quand la lisibilité prime et que le volume est borné de façon certaine.

Mais rappelez-vous : un bloc excellent invoqué dans un curseur perd ses gains (voir la démarche, §5 et les cas mesurés).

-- Boucle sur des OBJETS : légitime (SQL dynamique, un objet par itération)
DECLARE @sql nvarchar(max) =
    (SELECT STRING_AGG(CAST(N'ALTER INDEX ALL ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' REBUILD;' AS nvarchar(max)), CHAR(10))
     FROM   sys.tables AS t
     JOIN   sys.schemas AS s ON s.schema_id = t.schema_id);
-- PRINT @sql;                        -- relire avant d'exécuter
EXEC sys.sp_executesql @sql;
Règle pratique

Une boucle qui pilote des ensembles est une orchestration. Une boucle qui traite les lignes d'un ensemble est, presque toujours, un défaut de conception.

Les faux ensemblistes#

Une requête peut avoir l'air ensembliste et se comporter en boucle cachée :

  • Fonctions scalaires appelées dans le SELECT ou le WHERE : exécutées par ligne, sans parallélisme, avec une estimation de coût défaillante (partiellement corrigé par l'inlining depuis SQL Server 2019, sous conditions).
  • Fonctions table à instructions multiples : estimations de lignes limitées et matérialisation systématique.
  • Sous-requêtes corrélées exécutées pour chaque ligne d'un gros ensemble, quand une jointure ou une fenêtre suffirait.
  • CTE récursive utilisée comme compteur pour générer des séries : récursion niveau par niveau, bornée par MAXRECURSION.
  • Curseur déguisé : boucle WHILE qui lit « la ligne suivante » avec SELECT TOP (1) à chaque itération.
  • Appels de procédures stockées par ligne, avec un paramètre scalaire, sur des milliers de lignes.
  • Déclencheurs écrits comme si une seule ligne était traitée.
-- Faux ensembliste : la fonction scalaire est appelée pour chaque ligne
SELECT CommandeId, dbo.fn_TauxRemise(ClientId) AS Taux FROM dbo.Commandes;

-- Ensembliste : fonction table en ligne + APPLY, ou jointure directe
SELECT o.CommandeId, r.Taux
FROM   dbo.Commandes AS o
CROSS APPLY dbo.itvf_TauxRemise(o.ClientId) AS r;
-- Déclencheur écrit pour une ligne : BUG dès qu'une instruction touche plusieurs lignes
CREATE TRIGGER dbo.trg_Mauvais ON dbo.Commandes AFTER INSERT AS
BEGIN
    DECLARE @id int = (SELECT CommandeId FROM inserted);   -- échoue ou ignore des lignes si inserted en contient plusieurs
    -- suite du traitement, fondée sur @id
END;
GO

-- Version correcte : traiter inserted comme un ensemble
CREATE TRIGGER dbo.trg_Correct ON dbo.Commandes AFTER INSERT AS
BEGIN
    INSERT INTO dbo.Audit (CommandeId, DateAudit)
    SELECT CommandeId, SYSUTCDATETIME() FROM inserted;
END;

Garde-fous#

  1. Se demander à chaque boucle : sur quoi itère-t-elle ? Sur des objets ou des lots : probablement légitime. Sur des lignes de données : à justifier.
  2. Écrire d'abord la version ensembliste si elle semble possible ; ne revenir à la boucle qu'avec une raison documentée.
  3. Mesurer sur des volumes réalistes (voir Mesurer sérieusement) : ni l'intuition, ni le dogme ne remplacent une mesure.
  4. Isoler la partie séquentielle, et garder le reste ensembliste.
  5. Vérifier l'égalité des résultats entre l'ancienne et la nouvelle version avant de remplacer.

Sources et vérification#