Case C: permissions

One clean function, called users × facts: 200,000 calls versus none.

≈ 6 min readReading

In brief

Errors: E1, E2, E5, E11.
Loop: 200,000 function calls, 59 million logical reads (40 users).
Set-based: 3 statements, 14,000 reads, 0 calls.
Method: the access rule becomes a table, regenerated when its source changes.

On this page

Problem statement
The model and the function
The procedural version
Why it is a disaster
Measurements
The set-based redesign
Verifying equivalence
Maintaining the preparation
Takeaways

Problem statement#

Users belong to groups organized as a tree; access rights to scopes are granted to groups and inherited by descendant groups. For each user, we want the total of the facts in the scopes they are allowed to see (typically row-level security applied to a bulk computation).

The model and the function#

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
-- A user has access to a scope if one of their groups, or an ancestor group, holds the right
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
-- Data: 40 users, 200 groups in a tree, 50 scopes, 5,000 facts
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;

The procedural version#

-- For each user: the total of the facts they are allowed to see
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;

Code review: the function faithfully encapsulates the rule (membership, inheritance, rights); it is reusable everywhere; all the tables are indexed; the cursor is local and read-only; the update is targeted. Nothing to complain about.

Why it is a disaster#

Accumulated error	Where it hides
E1 invisible outer loop	One query per user
E2 scalar link	The function is called for every user and every fact; it is not inlineable (`is_inlineable = 0`: it contains a CTE), hence iterative and without parallelism
E5 derived data recomputed	The group hierarchy is traversed again on every call, even though rights rarely change
E11 parallelism neutralized	A non-inlined scalar function prevents the query from running in parallel

The number of calls is users × facts. With 40 users and 5,000 facts: 200,000 calls. At the scale of a real database (for example 1,000 users and 10 million facts), the same pattern would require 10¹⁰ calls (calculated, not measured): the workload would never finish.

Measurements#

Users	Variant	Statements	Function calls	Logical reads	CPU (ms)	Duration (ms)
10	P · procedural (function per user and per fact)	50,022	50,000	14,491,963	6,644	3,943
10	S · set-based (2 preparations + 1 query)	3	0	13,483	16	26
10	S2 · recalculation only	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

The loop is proportional to the number of users (50,000, 100,000, 200,000 calls) and its logical reads go from 14.5 to 59 million.
The set-based version stays almost constant: about 14,000 logical reads, 3 statements, no function calls.
The duration gap goes from about 150 to about 440 times as the number of users goes from 10 to 40 (indicative durations); in logical reads, it exceeds 4,000 times with 40 users.

The set-based redesign#

Two pieces of stable data are prepared: the inheritance between groups (a closure) and the access rule itself (a table of user–scope pairs). Execution is then nothing more than a join.

Preparation#

-- PREPARATION 1 — the group hierarchy becomes a closure table
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);

-- PREPARATION 2 — the access rule becomes a table: (user, scope)
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;

Execution: one query#

-- EXECUTION — a single query, all users
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;

What the preparation makes possible#

-- The access table also serves to FILTER: a join, never a function call per row
SELECT f.FaitId, f.Montant
FROM   dbo.Faits AS f
JOIN   dbo.Acces AS a ON a.PerimetreId = f.PerimetreId
WHERE  a.UtilisateurId = 17;

Verifying equivalence#

-- Equivalence: keep Resultat after the procedural version (Resultat_ref), reset Total to NULL, run the set-based version
SELECT * FROM dbo.Resultat_ref EXCEPT SELECT * FROM dbo.Resultat;     -- no rows
SELECT * FROM dbo.Resultat EXCEPT SELECT * FROM dbo.Resultat_ref;     -- no rows

Result: no rows in either direction; the access table contains 195, 391 then 756 pairs depending on the size, and no user is without access in the test data set.

Maintaining the preparation#

Acces is a function of Groupes, Appartenances and DroitsGroupes. It must be regenerated as soon as one of these three tables changes: that is the responsibility of the load that modifies them, not of the processing (see E10). A freshness check (expected number of pairs, periodic comparison with the original rule) prevents a revoked right from remaining visible: a stale preparation would, here, be a security hole.

Takeaways#

A "clean" function called per row and per entity multiplies the calls: users × facts.
An access rule is derived data: you materialize it (user–scope pairs), regenerate it when its source changes, and join to it.
The same preparation serves both the bulk computation and single-row filtering.