i

Description

Un centre de données vu du toit

Source

Rsparks3 · 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.

La programmation ensembliste en T-SQL

Décrire le résultat, pas le chemin : la manière de penser qui change l'échelle de ce que le SQL sait traiter.

En bref

• Objectif : exprimer le résultat attendu et laisser l'optimiseur choisir l'exécution.
• Principe : des ensembles en entrée, un ensemble en sortie, composés entre eux.
• Moyens : jointures, agrégats, fonctions de fenêtre, CTE, APPLY, écritures ensemblistes.
• Garde-fou : une boucle sur des objets est légitime ; sur des lignes de données, rarement.

Sur cette page

1. Objectif
2. Le principe en cinq idées
3. Procédural et ensembliste, d'un coup d'œil
4. Un exemple minimal
   1. Procédural
   2. Ensembliste
5. Plan de cette section
6. Sources et vérification

Objectif#

La programmation ensembliste consiste à exprimer le résultat que l'on veut (un ensemble de lignes défini par des propriétés), plutôt que la suite d'étapes qui le fabrique ligne après ligne. En T-SQL, cela revient à confier à l'optimiseur de requêtes le choix de la méthode d'exécution.

Ses bénéfices, quand on la pratique bien :

• Performance : une instruction traite des milliers de lignes en un passage, avec parallélisme, algorithmes de jointure adaptés et écritures groupées.
• Lisibilité : l'intention (« quoi ») est visible ; la mécanique (« comment ») est laissée au moteur.
• Fiabilité : une instruction est atomique ; pas d'état intermédiaire à gérer à la main.
• Maintenabilité : moins de variables, moins de branches, moins d'effets de bord.
• Passage à l'échelle : le coût croît le plus souvent de façon quasi linéaire avec le volume, au lieu d'exploser.

Le principe en cinq idées#

1. Une table est un ensemble de lignes, sans ordre. Aucune ligne n'est « la première » tant qu'on n'a pas demandé un tri.
2. Chaque opération prend des ensembles et rend un ensemble. On peut donc les composer : filtrer, joindre, regrouper, puis réutiliser le résultat.
3. On décrit la propriété du résultat (« les commandes de plus de deux ans »), pas l'algorithme pour les trouver.
4. L'optimiseur choisit le plan d'exécution à partir des statistiques et des index.
5. On pense « grain » : à quoi correspond une ligne du résultat (une vente, un client par mois, un jour) ? Presque toutes les erreurs viennent d'un grain mal défini.

Procédural et ensembliste, d'un coup d'œil#

Un exemple minimal#

Marquer comme archivées les commandes antérieures à 2020 :

-- Procédural : marquer les commandes anciennes, une par une
DECLARE @Id int;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT CommandeId FROM dbo.Commandes WHERE DateCommande < '2020-01-01';
OPEN c;
FETCH NEXT FROM c INTO @Id;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE dbo.Commandes SET Statut = 'ARCHIVEE' WHERE CommandeId = @Id;
    FETCH NEXT FROM c INTO @Id;
END;
CLOSE c;
DEALLOCATE c;

-- Ensembliste : une seule instruction, un seul plan
UPDATE dbo.Commandes
SET    Statut = 'ARCHIVEE'
WHERE  DateCommande < '2020-01-01';

La seconde forme exprime la même chose en une instruction. Le moteur peut utiliser un index sur DateCommande, regrouper les écritures dans le journal de transactions et, si la volumétrie le justifie, paralléliser. La première forme exécute autant d'instructions que de lignes : à chaque itération, une ligne est lue, une instruction est lancée, une ligne est modifiée.

Plan de cette section#

Sources et vérification#

• Codd, E. F. (1970), « A Relational Model of Data for Large Shared Data Banks », CACM 13(6), 377–387 — fondement du modèle relationnel
• Microsoft Learn — Clause OVER (Transact-SQL) — fonctions de fenêtre
• Microsoft Learn — Scalar UDF Inlining — coût des fonctions scalaires, évolutions de SQL Server 2019