Cas C : les habilitations

Une fonction propre, appelée utilisateurs × faits : 200 000 appels contre aucun.

≈ 6 min de lectureLecture

En bref

Erreurs : E1, E2, E5, E11.
Boucle : 200 000 appels de fonction, 59 millions de lectures logiques (40 utilisateurs).
Ensembliste : 3 instructions, 14 000 lectures, 0 appel.
Méthode : la règle d'accès devient une table, régénérée quand sa source change.

Sur cette page

Énoncé
Le modèle et la fonction
La version procédurale
Pourquoi c'est une catastrophe
Mesures
La refonte ensembliste
Vérifier l'équivalence
Maintenir la préparation
Ce qu'on retient

Énoncé#

Des utilisateurs appartiennent à des groupes organisés en arbre ; des droits d'accès à des périmètres sont attribués aux groupes et hérités par les groupes descendants. Pour chaque utilisateur, on veut le total des faits des périmètres qu'il a le droit de voir (typiquement une sécurité au niveau ligne appliquée à un calcul de masse).

Le modèle et la fonction#

CREATE TABLE dbo.Utilisateurs (UtilisateurId int NOT NULL PRIMARY KEY);
CREATE TABLE dbo.Groupes (GroupeId int NOT NULL PRIMARY KEY, ParentGroupeId int NULL);
CREATE TABLE dbo.Appartenances (UtilisateurId int NOT NULL, GroupeId int NOT NULL, CONSTRAINT PK_App PRIMARY KEY (UtilisateurId, GroupeId));
CREATE TABLE dbo.DroitsGroupes (GroupeId int NOT NULL, PerimetreId int NOT NULL, CONSTRAINT PK_Droits PRIMARY KEY (GroupeId, PerimetreId));
CREATE TABLE dbo.Faits (FaitId int NOT NULL PRIMARY KEY, PerimetreId int NOT NULL, Montant decimal(18,2) NOT NULL);
CREATE INDEX IX_Faits_Perim ON dbo.Faits (PerimetreId) INCLUDE (Montant);
CREATE TABLE dbo.Resultat (UtilisateurId int NOT NULL PRIMARY KEY, Total decimal(18,2) NULL);
GO
-- Un utilisateur a accès à un périmètre si l'un de ses groupes, ou un groupe ancêtre, en a le droit
CREATE FUNCTION dbo.f_EstAutorise (@UtilisateurId int, @PerimetreId int) RETURNS bit AS
BEGIN
    DECLARE @r bit = 0;
    WITH Ancetres AS
    (
        SELECT GroupeId FROM dbo.Appartenances WHERE UtilisateurId = @UtilisateurId
        UNION ALL
        SELECT gr.ParentGroupeId FROM dbo.Groupes AS gr JOIN Ancetres AS a ON gr.GroupeId = a.GroupeId WHERE gr.ParentGroupeId IS NOT NULL
    )
    SELECT @r = 1 WHERE EXISTS (SELECT 1 FROM Ancetres AS a JOIN dbo.DroitsGroupes AS d ON d.GroupeId = a.GroupeId WHERE d.PerimetreId = @PerimetreId);
    RETURN @r;
END;
GO
-- Données : 40 utilisateurs, 200 groupes en arbre, 50 périmètres, 5 000 faits
WITH N AS (SELECT TOP (200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns)
INSERT INTO dbo.Groupes SELECT n, CASE WHEN n = 1 THEN NULL ELSE (n - 2) / 3 + 1 END FROM N;
WITH N AS (SELECT TOP (40) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns)
INSERT INTO dbo.Utilisateurs SELECT n FROM N;
INSERT INTO dbo.Appartenances (UtilisateurId, GroupeId)
SELECT DISTINCT u.UtilisateurId, 20 + ((u.UtilisateurId * k.k * 37) % 180) FROM dbo.Utilisateurs AS u CROSS JOIN (VALUES (1),(2),(3)) AS k(k);
INSERT INTO dbo.DroitsGroupes (GroupeId, PerimetreId)
SELECT DISTINCT g.GroupeId, ((g.GroupeId * k.k * 13) % 50) + 1 FROM dbo.Groupes AS g CROSS JOIN (VALUES (1),(2)) AS k(k);
WITH N AS (SELECT TOP (5000) 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.Faits SELECT n, (n % 50) + 1, (n % 97) / 10.0 FROM N;
INSERT INTO dbo.Resultat (UtilisateurId) SELECT UtilisateurId FROM dbo.Utilisateurs;
UPDATE STATISTICS dbo.Faits WITH FULLSCAN;

La version procédurale#

-- Pour chaque utilisateur : le total des faits qu'il a le droit de voir
DECLARE @u int;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT UtilisateurId FROM dbo.Utilisateurs ORDER BY UtilisateurId;
OPEN c; FETCH NEXT FROM c INTO @u;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE dbo.Resultat
    SET    Total = (SELECT SUM(f.Montant) FROM dbo.Faits AS f WHERE dbo.f_EstAutorise(@u, f.PerimetreId) = 1)
    WHERE  UtilisateurId = @u;
    FETCH NEXT FROM c INTO @u;
END;
CLOSE c; DEALLOCATE c;

Revue de code : la fonction encapsule fidèlement la règle (appartenance, héritage, droits) ; elle est réutilisable partout ; toutes les tables sont indexées ; le curseur est local et en lecture seule ; la mise à jour est ciblée. Rien à redire.

Pourquoi c'est une catastrophe#

Erreur cumulée	Où elle se cache
E1 boucle externe invisible	Une requête par utilisateur
E2 maillon scalaire	La fonction est appelée pour chaque utilisateur et chaque fait ; elle n'est pas inlinable (`is_inlineable = 0` : elle contient une CTE), donc itérative et sans parallélisme
E5 dérivée recalculée	La hiérarchie de groupes est reparcourue à chaque appel, alors que les droits changent rarement
E11 parallélisme neutralisé	Une fonction scalaire non inlinée interdit le parallélisme de la requête

Le nombre d'appels est utilisateurs × faits. Avec 40 utilisateurs et 5 000 faits : 200 000 appels. À l'échelle d'une vraie base (par exemple 1 000 utilisateurs et 10 millions de faits), le même schéma exigerait 10¹⁰ appels (calcul, non mesuré) : le traitement ne se termine plus.

Mesures#

Utilisateurs	Variante	Instructions	Appels de fonction	Lectures logiques	CPU (ms)	Durée (ms)
10	P · procédural (fonction par utilisateur et par fait)	50 022	50 000	14 491 963	6 644	3 943
10	S · ensembliste (2 préparations + 1 requête)	3	0	13 483	16	26
10	S2 · recalcul seul	1	0	684	8	10
20	P	100 042	100 000	29 065 135	13 341	7 936
20	S	3	0	13 312	19	34
20	S2	1	0	61	9	7
40	P	200 082	200 000	59 173 555	27 429	16 338
40	S	3	0	14 218	25	37
40	S2	1	0	103	16	19

La boucle est proportionnelle au nombre d'utilisateurs (50 000, 100 000, 200 000 appels) et ses lectures logiques passent de 14,5 à 59 millions.
La version ensembliste reste quasi constante : environ 14 000 lectures logiques, 3 instructions, aucun appel de fonction.
L'écart de durée passe d'environ 150 à environ 440 fois quand le nombre d'utilisateurs passe de 10 à 40 (durées indicatives) ; en lectures logiques, il dépasse 4 000 fois avec 40 utilisateurs.

La refonte ensembliste#

Deux données stables sont préparées : l'héritage entre groupes (une fermeture) et la règle d'accès elle-même (une table de couples utilisateur–périmètre). L'exécution n'est plus qu'une jointure.

Préparation#

-- PRÉPARATION 1 — la hiérarchie de groupes devient une table de fermeture
CREATE TABLE dbo.GroupeFermeture (Groupe int NOT NULL, Ancetre int NOT NULL, CONSTRAINT PK_GF PRIMARY KEY (Groupe, Ancetre));
WITH F AS
(
    SELECT GroupeId AS Groupe, GroupeId AS Ancetre FROM dbo.Groupes
    UNION ALL
    SELECT f.Groupe, gr.ParentGroupeId FROM F AS f JOIN dbo.Groupes AS gr ON gr.GroupeId = f.Ancetre WHERE gr.ParentGroupeId IS NOT NULL
)
INSERT INTO dbo.GroupeFermeture (Groupe, Ancetre) SELECT Groupe, Ancetre FROM F OPTION (MAXRECURSION 200);

-- PRÉPARATION 2 — la règle d'accès devient une table : (utilisateur, périmètre)
CREATE TABLE dbo.Acces (UtilisateurId int NOT NULL, PerimetreId int NOT NULL, CONSTRAINT PK_Acces PRIMARY KEY (PerimetreId, UtilisateurId));
INSERT INTO dbo.Acces (UtilisateurId, PerimetreId)
SELECT DISTINCT a.UtilisateurId, d.PerimetreId
FROM   dbo.Appartenances AS a
JOIN   dbo.GroupeFermeture AS gf ON gf.Groupe = a.GroupeId
JOIN   dbo.DroitsGroupes   AS d  ON d.GroupeId = gf.Ancetre;

Exécution : une requête#

-- EXÉCUTION — une seule requête, tous les utilisateurs
UPDATE r
SET    r.Total = s.Total
FROM   dbo.Resultat AS r
JOIN  (SELECT u.UtilisateurId, SUM(f.Montant) AS Total
       FROM   dbo.Utilisateurs AS u
       LEFT JOIN dbo.Acces AS a ON a.UtilisateurId = u.UtilisateurId
       LEFT JOIN dbo.Faits AS f ON f.PerimetreId = a.PerimetreId
       GROUP BY u.UtilisateurId) AS s ON s.UtilisateurId = r.UtilisateurId;

Ce que la préparation rend possible#

-- La table d'accès sert aussi à FILTRER : une jointure, jamais un appel de fonction par ligne
SELECT f.FaitId, f.Montant
FROM   dbo.Faits AS f
JOIN   dbo.Acces AS a ON a.PerimetreId = f.PerimetreId
WHERE  a.UtilisateurId = 17;

Vérifier l'équivalence#

-- Équivalence : conserver Resultat après la version procédurale (Resultat_ref), remettre Total à NULL, exécuter la version ensembliste
SELECT * FROM dbo.Resultat_ref EXCEPT SELECT * FROM dbo.Resultat;     -- aucune ligne
SELECT * FROM dbo.Resultat EXCEPT SELECT * FROM dbo.Resultat_ref;     -- aucune ligne

Résultat : aucune ligne dans les deux sens ; la table d'accès contient 195, 391 puis 756 couples selon la taille, et aucun utilisateur n'est sans accès dans le jeu d'essai.

Maintenir la préparation#

Acces est une fonction de Groupes, Appartenances et DroitsGroupes. Elle doit être régénérée dès que l'une de ces trois tables change : c'est la responsabilité du chargement qui les modifie, pas du traitement (voir E10). Un contrôle de fraîcheur (nombre de couples attendu, comparaison périodique avec la règle d'origine) évite qu'un droit retiré reste visible : une préparation périmée, ici, serait une faille de sécurité.

Ce qu'on retient#

Une fonction « propre » appelée par ligne et par entité multiplie les appels : utilisateurs × faits.
Une règle d'accès est une donnée dérivée : on la matérialise (couples utilisateur–périmètre), on la rejoue quand sa source change, et on la joint.
La même préparation sert au calcul de masse et au filtrage unitaire.