i

Description

Une presse d'imprimerie

Source

John Farey · 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.

Générer du code : inlining et valeurs en dur

Payer une fois pour que chaque exécution coûte moins : le résultat d'un SELECT de référence devient une valeur littérale que l'optimiseur peut exploiter.

En bref

• Problème : l'optimiseur ignore le contenu des référentiels joints.
• Mesure : 100 lignes réelles ; 25 000 estimées avec jointure, variable ou paramètre table ; 100 avec littéral, RECOMPILE ou objet généré.
• Inline : fonction table en ligne, inlining scalaire (2019), dépliage généré.
• Règle : un objet généré, déterministe, reproductible, régénéré quand le référentiel change.

Sur cette page

1. 1. Le problème : l'optimiseur ne connaît pas le contenu de vos référentiels
2. 2. Mesure : ce que l'optimiseur croit selon l'écriture
3. 3. L'idée : déplier le référentiel dans un objet généré
4. 4. Les trois sens d'« inline »
5. 5. Générer une fonction déterministe
6. 6. Index filtrés : le littéral fait la différence
7. 7. Quand cela vaut-il la peine ?
8. 8. Générer proprement
9. 9. Risques
10. 10. Alternatives
11. Sources et vérification

« Compiler à la volée » consiste à produire le texte d'un objet T-SQL à partir de données, puis à le faire compiler par le moteur. Appliqué à un petit référentiel, cela permet d'écrire en dur le résultat d'un SELECT dans le code, et de donner à l'optimiseur des valeurs qu'il n'aurait pas devinées. Cette page explique l'intérêt, le mécanisme, ses limites et la bonne manière de s'y prendre.

1. Le problème : l'optimiseur ne connaît pas le contenu de vos référentiels#

Une requête qui filtre par « la zone Amérique » doit d'abord retrouver, par jointure, les pays de cette zone. Au moment de choisir son plan, l'optimiseur ne sait pas lesquels, donc quelle part des données ils représentent. Il s'appuie alors sur des statistiques moyennes. Avec des données déséquilibrées, l'écart à la réalité peut être de plusieurs ordres de grandeur.

2. Mesure : ce que l'optimiseur croit selon l'écriture#

Jeu d'essai : 100 000 faits répartis très inégalement entre quatre pays (la zone « AM » ne contient que les États-Unis, 100 lignes).

CREATE TABLE dbo.Zones (ZoneId int NOT NULL PRIMARY KEY, Code nvarchar(10) NOT NULL);
CREATE TABLE dbo.Pays  (PaysId int NOT NULL PRIMARY KEY, Code nchar(2) NOT NULL, ZoneId int NOT NULL REFERENCES dbo.Zones (ZoneId));
CREATE TABLE dbo.Faits (FaitId int NOT NULL PRIMARY KEY, PaysId int NOT NULL, Statut tinyint NOT NULL, Montant decimal(18,2) NOT NULL);
CREATE INDEX IX_Faits_Pays ON dbo.Faits (PaysId) INCLUDE (Statut, Montant);

INSERT INTO dbo.Zones VALUES (1, N'EU'), (2, N'AM');
INSERT INTO dbo.Pays  VALUES (1, N'FR', 1), (2, N'DE', 1), (3, N'IT', 1), (4, N'US', 2);

-- 100 000 faits très déséquilibrés : FR 98 000 · DE 1 000 · IT 900 · US 100
WITH N AS (SELECT TOP (100000) 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 (FaitId, PaysId, Statut, Montant)
SELECT n,
       CASE WHEN n <= 98000 THEN 1 WHEN n <= 99000 THEN 2 WHEN n <= 99900 THEN 3 ELSE 4 END,
       CASE WHEN n % 1000 = 0 THEN 9 ELSE 1 END,          -- Statut 9 : 100 lignes seulement
       (n % 500) / 10.0
FROM N;
UPDATE STATISTICS dbo.Faits WITH FULLSCAN;

-- a) jointure vers les référentiels
SELECT f.FaitId, f.Montant
FROM   dbo.Faits AS f
JOIN   dbo.Pays  AS p ON p.PaysId = f.PaysId
JOIN   dbo.Zones AS z ON z.ZoneId = p.ZoneId
WHERE  z.Code = N'AM';
GO
-- b) variable locale
DECLARE @p int = 4;
SELECT FaitId, Montant FROM dbo.Faits WHERE PaysId = @p;
GO
-- c) variable + OPTION (RECOMPILE)
DECLARE @p int = 4;
SELECT FaitId, Montant FROM dbo.Faits WHERE PaysId = @p OPTION (RECOMPILE);
GO
-- d) littéral
SELECT FaitId, Montant FROM dbo.Faits WHERE PaysId = 4;

-- Pour lire les estimations : SET STATISTICS XML ON avant la requête,
-- ou « Inclure le plan d'exécution réel » dans SSMS, puis comparer « Estimated Number of Rows ».

Les trois écritures qui connaissent la valeur (c, d, e) estiment 100 lignes ; les trois qui ne la connaissent pas (a, b, paramètre table) estiment 25 000 ou 50 000. Les explications s'appuient sur la documentation : « la valeur d'une variable locale n'est pas connue à l'optimisation », alors que OPTION (RECOMPILE) « transforme les paramètres et variables locales en constantes de compilation ».

3. L'idée : déplier le référentiel dans un objet généré#

Au lieu de joindre le référentiel à chaque exécution, on génère un objet dont le texte contient les valeurs :

-- Génération (au déploiement, ou à chaque mise à jour du référentiel)
DECLARE @liste nvarchar(max) =
    (SELECT STRING_AGG(CAST(p.PaysId AS nvarchar(20)), N',') WITHIN GROUP (ORDER BY p.PaysId)
     FROM   dbo.Pays AS p
     JOIN   dbo.Zones AS z ON z.ZoneId = p.ZoneId
     WHERE  z.Code = N'AM');

DECLARE @sql nvarchar(max) =
    N'CREATE OR ALTER VIEW dbo.v_Faits_AM AS' + NCHAR(10) +
    N'SELECT FaitId, PaysId, Montant FROM dbo.Faits WHERE PaysId IN (' + ISNULL(@liste, N'NULL') + N');';

PRINT @sql;                      -- on garde une trace du code généré
EXEC sys.sp_executesql @sql;     -- compilation de l'objet

-- Texte produit :
--   CREATE OR ALTER VIEW dbo.v_Faits_AM AS
--   SELECT FaitId, PaysId, Montant FROM dbo.Faits WHERE PaysId IN (4);

-- Équivalence avec la version par jointure, dans les deux sens (aucune ligne attendue)
SELECT f.FaitId FROM dbo.Faits AS f JOIN dbo.Pays AS p ON p.PaysId = f.PaysId JOIN dbo.Zones AS z ON z.ZoneId = p.ZoneId
WHERE z.Code = N'AM'
EXCEPT SELECT FaitId FROM dbo.v_Faits_AM;

SELECT FaitId FROM dbo.v_Faits_AM
EXCEPT
SELECT f.FaitId FROM dbo.Faits AS f JOIN dbo.Pays AS p ON p.PaysId = f.PaysId JOIN dbo.Zones AS z ON z.ZoneId = p.ZoneId
WHERE z.Code = N'AM';

La vue générée est estimée à 100 lignes (ligne e du tableau) : même précision qu'un littéral, sans recompilation à chaque appel. Si la zone n'existe pas, la liste vide est remplacée par IN (NULL) et la vue retourne zéro ligne.

4. Les trois sens d'« inline »#

Le dépliage généré est le seul des trois qui ne dépend d'aucune règle du moteur : c'est vous qui décidez de ce qui est « mis en ligne ». Il répond aussi à des cas que les deux autres ne couvrent pas (valeurs de référence, index filtrés).

5. Générer une fonction déterministe#

Le même principe permet de remplacer une fonction scalaire qui interroge un référentiel par une fonction qui contient ce référentiel (un CASE) :

-- Génération d'une fonction scalaire déterministe à partir du référentiel
DECLARE @cases nvarchar(max) =
    (SELECT STRING_AGG(CAST(N'WHEN ' + CAST(PaysId AS nvarchar(20)) + N' THEN N' + QUOTENAME(Code, '''') AS nvarchar(max)), NCHAR(10))
            WITHIN GROUP (ORDER BY PaysId)
     FROM dbo.Pays);

DECLARE @sql nvarchar(max) =
    N'CREATE OR ALTER FUNCTION dbo.f_CodePays (@PaysId int) RETURNS nchar(2) WITH SCHEMABINDING AS' + NCHAR(10) +
    N'BEGIN' + NCHAR(10) +
    N'RETURN CASE @PaysId' + NCHAR(10) + @cases + NCHAR(10) + N'ELSE NULL END;' + NCHAR(10) +
    N'END;';

PRINT @sql;
EXEC sys.sp_executesql @sql;

-- Vérifications : déterministe, et candidate à l'inlining (SQL Server 2019)
SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo.f_CodePays'), 'IsDeterministic') AS est_deterministe;
SELECT is_inlineable FROM sys.sql_modules WHERE object_id = OBJECT_ID(N'dbo.f_CodePays');
GO
-- Parce qu'elle est déterministe : colonne calculée persistée et indexée
ALTER TABLE dbo.Faits ADD CodePays AS dbo.f_CodePays(PaysId) PERSISTED;
GO
CREATE INDEX IX_Faits_CodePays ON dbo.Faits (CodePays);
GO

Texte produit :

CREATE OR ALTER FUNCTION dbo.f_CodePays (@PaysId int) RETURNS nchar(2) WITH SCHEMABINDING AS
BEGIN
RETURN CASE @PaysId
WHEN 1 THEN N'FR'
WHEN 2 THEN N'DE'
WHEN 3 THEN N'IT'
WHEN 4 THEN N'US'
ELSE NULL END;
END;

Les vérifications donnent est_deterministe = 1 et is_inlineable = 1. Comme la fonction est déterministe et liée au schéma, elle peut alimenter une colonne calculée persistée et indexée (voir Déterminisme) : le code de pays est stocké et indexé, plus aucun calcul à la lecture.

6. Index filtrés : le littéral fait la différence#

Pour qu'un index filtré serve une requête, le prédicat de la requête doit être un sous-ensemble de celui de l'index (documentation). Avec une variable, le plan doit rester valable quelle que soit la valeur : l'optimiseur ne peut pas toujours démontrer l'inclusion.

CREATE INDEX IX_Faits_Statut9 ON dbo.Faits (PaysId) INCLUDE (Montant) WHERE Statut = 9;   -- index filtré

GO
-- a) littéral
SELECT PaysId, Montant FROM dbo.Faits WHERE Statut = 9;
GO
-- b) variable
DECLARE @s tinyint = 9;
SELECT PaysId, Montant FROM dbo.Faits WHERE Statut = @s;
GO
-- c) variable + OPTION (RECOMPILE)
DECLARE @s tinyint = 9;
SELECT PaysId, Montant FROM dbo.Faits WHERE Statut = @s OPTION (RECOMPILE);

7. Quand cela vaut-il la peine ?#

8. Générer proprement#

1. Un générateur déterministe : à référentiel identique, texte identique (ordre explicite, pas d'horodatage dans le corps) ; les différences entre deux générations deviennent lisibles.
2. Idempotent : CREATE OR ALTER (SQL Server 2016 SP1 et ultérieur) ; rejouable à volonté.
3. Sûr : QUOTENAME, CAST, liste vide traitée, taille maîtrisée.
4. Tracé : texte généré journalisé, date et empreinte du référentiel en propriété étendue.
5. Déclenché au bon moment : à la mise à jour du référentiel, ou dans le déploiement.
6. Testé : équivalence avec la version par jointure (EXCEPT dans les deux sens), puis estimation et plan.
7. Déterministe côté objet : SCHEMABINDING et aucune fonction dépendante du temps, pour ouvrir l'inlining et l'indexation.

-- Générer proprement : validations et gabarit
DECLARE @liste nvarchar(max) = (SELECT STRING_AGG(CAST(PaysId AS nvarchar(20)), N',') WITHIN GROUP (ORDER BY PaysId)
                                FROM dbo.Pays AS p JOIN dbo.Zones AS z ON z.ZoneId = p.ZoneId WHERE z.Code = N'XX');   -- zone inconnue
-- 1) liste vide : IN () est une erreur de syntaxe -> IN (NULL), qui ne retourne aucune ligne
-- 2) valeurs numériques : CAST AS nvarchar ; chaînes : QUOTENAME(valeur, '''') ; jamais de concaténation brute
-- 3) ordre explicite (WITHIN GROUP) : le texte généré est identique d'une exécution à l'autre
-- 4) pas d'horodatage dans le corps de l'objet : la date de génération va dans une propriété étendue
DECLARE @sql nvarchar(max) =
    N'CREATE OR ALTER VIEW dbo.v_Faits_XX AS' + NCHAR(10) +
    N'SELECT FaitId, PaysId, Montant FROM dbo.Faits WHERE PaysId IN (' + ISNULL(@liste, N'NULL') + N');';
EXEC sys.sp_executesql @sql;
SELECT COUNT(*) AS nb_lignes FROM dbo.v_Faits_XX;      -- 0

9. Risques#

10. Alternatives#

Sources et vérification#

• Microsoft Learn — Query processing architecture guide — repliement de constantes, variables et paramètres, RECOMPILE, plans en cache, sp_executesql
• Microsoft Learn — Create filtered indexes — le prédicat de la requête doit être un sous-ensemble de celui de l'index
• Microsoft Learn — Scalar UDF Inlining — inlining des fonctions scalaires (SQL Server 2019)
• Microsoft Learn — Deterministic and nondeterministic functions — déterminisme et SCHEMABINDING
• Microsoft Learn — STRING_AGG — agrégation du texte généré
• Microsoft Learn — Query hints (RECOMPILE) — indice RECOMPILE