i

Description

Des lingots d'or

Source

Szaaman · page d’origine

Licence

PD Domaine public

Photo d’origine, non retouchée. L’arrière-plan en est une version stylisée par le site.

Cas A : la valorisation par client

Six erreurs cumulées, aucune dans une requête : 188 002 instructions contre 4.

En bref

• Erreurs : E1, E2, E3, E4, E5, E13.
• Boucle : 188 002 instructions, 180 000 appels de fonction (2 000 clients).
• Ensembliste : 4 instructions, 0 appel.
• Méthode : périmètre en table, fonction matérialisée, une requête.

Sur cette page

1. Énoncé
2. Le modèle et la procédure
3. La version procédurale
4. Pourquoi c'est une catastrophe
5. Mesures
6. La refonte ensembliste
   1. Préparation
   2. Exécution : une requête
7. Vérifier l'équivalence
8. Maintenir la préparation
9. Ce qu'on retient

Énoncé#

Chaque jour, la consommation de chaque client est valorisée selon un barème par segment, dont les taux changent à des dates précises. Avec 2 000 clients et 90 jours de consommation : 180 000 lignes à valoriser, avec une trace d'audit pour chacune.

Le modèle et la procédure#

CREATE TABLE dbo.Clients (ClientId int NOT NULL PRIMARY KEY, SegmentId tinyint NOT NULL);
CREATE TABLE dbo.Taux    (SegmentId tinyint NOT NULL, DateDebut date NOT NULL, Taux decimal(9,4) NOT NULL,
                          CONSTRAINT PK_Taux PRIMARY KEY (SegmentId, DateDebut));
-- Séries chronologiques : la table est rangée par date (chargement quotidien en fin de table)
CREATE TABLE dbo.Consommations (ClientId int NOT NULL, Jour date NOT NULL, Quantite decimal(18,3) NOT NULL,
                                CONSTRAINT PK_Conso PRIMARY KEY CLUSTERED (Jour, ClientId));
CREATE TABLE dbo.Valorisations (ClientId int NOT NULL, Jour date NOT NULL, Montant decimal(18,4) NOT NULL,
                                CONSTRAINT PK_Valo PRIMARY KEY (ClientId, Jour));
CREATE TABLE dbo.Audit_Valo (AuditId int IDENTITY(1,1) PRIMARY KEY, ClientId int NOT NULL, Jour date NOT NULL,
                             Montant decimal(18,4) NOT NULL, DateAudit datetime2 NOT NULL DEFAULT SYSUTCDATETIME());
GO
-- Déclencheur d'audit : traite tout l'ensemble « inserted »
CREATE TRIGGER dbo.trg_Valo ON dbo.Valorisations AFTER INSERT AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.Audit_Valo (ClientId, Jour, Montant) SELECT ClientId, Jour, Montant FROM inserted;
END;
GO
-- Taux applicable à un segment et à un jour : recherche par clé primaire, 0 si aucun taux
CREATE FUNCTION dbo.f_TauxApplicable (@SegmentId tinyint, @Jour date) RETURNS decimal(9,4) AS
BEGIN
    DECLARE @t decimal(9,4);
    SELECT TOP (1) @t = Taux FROM dbo.Taux WHERE SegmentId = @SegmentId AND DateDebut <= @Jour ORDER BY DateDebut DESC;
    IF @t IS NULL RETURN 0;
    RETURN @t;
END;
GO
-- Valorise UN client : une seule instruction, ensembliste
CREATE PROCEDURE dbo.p_ValoriserClient @ClientId int AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Seg tinyint = (SELECT SegmentId FROM dbo.Clients WHERE ClientId = @ClientId);
    INSERT INTO dbo.Valorisations (ClientId, Jour, Montant)
    SELECT c.ClientId, c.Jour, c.Quantite * dbo.f_TauxApplicable(@Seg, c.Jour)
    FROM   dbo.Consommations AS c
    WHERE  c.ClientId = @ClientId;
END;
GO

-- 500 clients (mettre 2000 pour reproduire le dernier jeu de mesures), 5 segments, 90 jours chacun, 4 périodes de taux par segment
WITH N AS (SELECT TOP (500) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b)
INSERT INTO dbo.Clients SELECT n, (n % 5) + 1 FROM N;

INSERT INTO dbo.Taux (SegmentId, DateDebut, Taux)
SELECT s.v, d.dt, 0.10 + s.v * 0.01 + d.k * 0.005
FROM (VALUES (1),(2),(3),(4),(5)) AS s(v)
CROSS JOIN (VALUES (1,'2025-06-01'),(2,'2025-12-01'),(3,'2026-02-01'),(4,'2026-03-15')) AS d(k, dt);

WITH J AS (SELECT TOP (90) CAST(DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '2026-01-01') AS date) AS Jour FROM sys.all_columns)
INSERT INTO dbo.Consommations (ClientId, Jour, Quantite)
SELECT c.ClientId, j.Jour, ((c.ClientId * 7 + DATEDIFF(DAY, '2026-01-01', j.Jour) * 3) % 100) / 10.0
FROM dbo.Clients AS c CROSS JOIN J AS j;
UPDATE STATISTICS dbo.Consommations WITH FULLSCAN;

La version procédurale#

-- L'orchestrateur : un curseur local en lecture seule, qui appelle la procédure pour chaque client
DECLARE @id int;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT ClientId FROM dbo.Clients ORDER BY ClientId;
OPEN c; FETCH NEXT FROM c INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.p_ValoriserClient @id;
    FETCH NEXT FROM c INTO @id;
END;
CLOSE c; DEALLOCATE c;

Revue de code : rien à redire.

Pourquoi c'est une catastrophe#

Aucune erreur n'est dans une requête. Elles sont dans l'assemblage :

Et elles se multiplient : 2 000 appels × 90 lignes × une fonction non inlinée, avec un parcours complet de la table à chaque appel.

Mesures#

Croissance quand le nombre de clients double :

La boucle sans index croît plus vite que le volume, parce que chaque appel parcourt une table qui grossit avec le nombre de clients : nombre d'appels × taille de la table. Ajouter l'index client corrige ce point (variante P1), mais la boucle demeure : 188 002 instructions et 180 000 appels de fonction contre 4 instructions. La meilleure boucle possible reste une boucle.

-- Variante « boucle optimisée au mieux » : on ajoute l'index que la boucle réclame
CREATE INDEX IX_Conso_Client ON dbo.Consommations (ClientId, Jour) INCLUDE (Quantite);

La refonte ensembliste#

Trois décisions, prises avant d'écrire la requête : le périmètre (les jours à valoriser) est une table ; la fonction « taux applicable » est matérialisée sur son domaine (segments × jours) ; la requête finale ne fait que joindre.

Préparation#

-- PRÉPARATION 1 — le périmètre : les jours à valoriser
CREATE TABLE dbo.JoursPerimetre (Jour date NOT NULL PRIMARY KEY);
INSERT INTO dbo.JoursPerimetre (Jour) SELECT DISTINCT Jour FROM dbo.Consommations;

-- PRÉPARATION 2 — la fonction « taux applicable » devient une table : sa table de vérité sur le domaine utile (segments x jours)
CREATE TABLE dbo.TauxParJour (SegmentId tinyint NOT NULL, Jour date NOT NULL, Taux decimal(9,4) NOT NULL,
                              CONSTRAINT PK_TauxParJour PRIMARY KEY (SegmentId, Jour));
WITH T AS (SELECT SegmentId, DateDebut, Taux, LEAD(DateDebut) OVER (PARTITION BY SegmentId ORDER BY DateDebut) AS DateFin FROM dbo.Taux)
INSERT INTO dbo.TauxParJour (SegmentId, Jour, Taux)
SELECT t.SegmentId, d.Jour, t.Taux
FROM   T AS t
JOIN   dbo.JoursPerimetre AS d ON d.Jour >= t.DateDebut AND (t.DateFin IS NULL OR d.Jour < t.DateFin);

Exécution : une requête#

-- EXÉCUTION — une seule requête, tous les clients, tous les jours, aucune itération
INSERT INTO dbo.Valorisations (ClientId, Jour, Montant)
SELECT c.ClientId, c.Jour, c.Quantite * ISNULL(tj.Taux, 0)
FROM   dbo.Consommations AS c
JOIN   dbo.Clients       AS cl ON cl.ClientId = c.ClientId
LEFT JOIN dbo.TauxParJour AS tj ON tj.SegmentId = cl.SegmentId AND tj.Jour = c.Jour;

Le déclencheur d'audit est conservé : il se déclenche une fois, pour tout l'ensemble inséré. Les lectures logiques sont environ 5 à 6 fois moindres que celles des deux variantes de boucle (755 297 contre 3 580 828 et 4 702 703 avec 2 000 clients) et la table n'a plus besoin d'aucun index « pour la boucle » : on la lit en un seul passage.

Vérifier l'équivalence#

-- Équivalence : conserver le résultat de la version procédurale (table Valo_ref), puis comparer dans les deux sens
SELECT * INTO dbo.Valo_ref FROM dbo.Valorisations;      -- après la version procédurale
TRUNCATE TABLE dbo.Valorisations; TRUNCATE TABLE dbo.Audit_Valo;
-- ... exécuter la version ensembliste ...
SELECT * FROM dbo.Valo_ref EXCEPT SELECT * FROM dbo.Valorisations;        -- aucune ligne
SELECT * FROM dbo.Valorisations EXCEPT SELECT * FROM dbo.Valo_ref;        -- aucune ligne

Résultat : aucune ligne dans les deux sens (aux trois tailles mesurées) ; avec le jeu de la page (500 clients), 45 000 lignes de valorisation et autant de lignes d'audit dans les deux versions.

Maintenir la préparation#

TauxParJour est une fonction de Taux et de JoursPerimetre : elle reste exacte tant que ces deux tables ne changent pas. Quand le barème change ou que de nouveaux jours entrent dans le périmètre, on régénère la table (TRUNCATE puis rechargement : quelques lignes, quelques millisecondes), dans le chargement qui modifie le barème. Voir E10.

Ce qu'on retient#

• Une procédure propre, appelée N fois, est une boucle.
• Une fonction scalaire non inlinée dans un traitement de masse fait perdre le parallélisme et multiplie les appels.
• Un index ajouté « pour la boucle » est un aveu : il compense, il ne corrige pas.
• Le remède est une forme différente : préparer (périmètre, fonction matérialisée), puis une requête.