i

Description

Gold bars

Source

Szaaman · original page

Licence

PD Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

Case A: valuation per customer

Six accumulated errors, none in a query: 188,002 statements versus 4.

In brief

• Errors: E1, E2, E3, E4, E5, E13.
• Loop: 188,002 statements, 180,000 function calls (2,000 customers).
• Set-based: 4 statements, 0 calls.
• Method: scope in a table, materialized function, one query.

On this page

1. Problem statement
2. The model and the procedure
3. The procedural version
4. Why it is a disaster
5. Measurements
6. The set-based redesign
   1. Preparation
   2. Execution: one query
7. Verifying equivalence
8. Maintaining the preparation
9. Takeaways

Problem statement#

Every day, each customer's consumption is valued according to a rate schedule per segment, whose rates change on specific dates. With 2,000 customers and 90 days of consumption: 180,000 rows to value, each with an audit trail.

The model and the procedure#

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));
-- Time series: the table is ordered by date (daily load at the end of the 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
-- Audit trigger: processes the whole "inserted" set
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
-- Rate applicable to a segment and a day: primary key lookup, 0 if there is no rate
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
-- Values ONE customer: a single statement, set-based
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 customers (use 2000 to reproduce the last set of measurements), 5 segments, 90 days each, 4 rate periods per 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;

The procedural version#

-- The orchestrator: a local read-only cursor that calls the procedure for each customer
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;

Code review: nothing to complain about.

Why it is a disaster#

No error is in a query. They are in the assembly:

And they multiply: 2,000 calls × 90 rows × a non-inlined function, with a full table scan on every call.

Measurements#

Growth when the number of customers doubles:

The loop without an index grows faster than the volume, because each call scans a table that grows with the number of customers: number of calls × table size. Adding the customer index fixes that point (variant P1), but the loop remains: 188,002 statements and 180,000 function calls versus 4 statements. The best possible loop is still a loop.

-- "Best-optimized loop" variant: we add the index that the loop demands
CREATE INDEX IX_Conso_Client ON dbo.Consommations (ClientId, Jour) INCLUDE (Quantite);

The set-based redesign#

Three decisions, made before writing the query: the scope (the days to value) is a table; the "applicable rate" function is materialized over its domain (segments × days); the final query only joins.

Preparation#

-- PREPARATION 1 — the scope: the days to value
CREATE TABLE dbo.JoursPerimetre (Jour date NOT NULL PRIMARY KEY);
INSERT INTO dbo.JoursPerimetre (Jour) SELECT DISTINCT Jour FROM dbo.Consommations;

-- PREPARATION 2 — the "applicable rate" function becomes a table: its truth table over the useful domain (segments x days)
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);

Execution: one query#

-- EXECUTION — a single query, all customers, all days, no iteration
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;

The audit trigger is kept: it fires once, for the whole inserted set. Logical reads are about 5 to 6 times lower than those of the two loop variants (755,297 versus 3,580,828 and 4,702,703 with 2,000 customers) and the table no longer needs any index "for the loop": it is read in a single pass.

Verifying equivalence#

-- Equivalence: keep the result of the procedural version (table Valo_ref), then compare in both directions
SELECT * INTO dbo.Valo_ref FROM dbo.Valorisations;      -- after the procedural version
TRUNCATE TABLE dbo.Valorisations; TRUNCATE TABLE dbo.Audit_Valo;
-- ... run the set-based version ...
SELECT * FROM dbo.Valo_ref EXCEPT SELECT * FROM dbo.Valorisations;        -- no rows
SELECT * FROM dbo.Valorisations EXCEPT SELECT * FROM dbo.Valo_ref;        -- no rows

Result: no rows in either direction (at the three measured sizes); with the data set on this page (500 customers), 45,000 valuation rows and as many audit rows in both versions.

Maintaining the preparation#

TauxParJour is a function of Taux and JoursPerimetre: it stays accurate as long as these two tables do not change. When the rate schedule changes or new days enter the scope, you regenerate the table (TRUNCATE then reload: a few rows, a few milliseconds), in the load that modifies the schedule. See E10.

Takeaways#

• A clean procedure, called N times, is a loop.
• A non-inlined scalar function in a bulk workload loses parallelism and multiplies the calls.
• An index added "for the loop" is an admission: it compensates, it does not fix.
• The remedy is a different shape: prepare (scope, materialized function), then one query.