Incorporation (inlining) des fonctions UDF scalaires

S’applique à : SQL Server 2019 (15.x) Azure SQL DatabaseAzure SQL Managed InstancePoint de terminaison d'analyse SQL dans Microsoft FabricWarehouse dans Microsoft FabricSQL Database dans Microsoft Fabric

Cet article présente l’incorporation des fonctions scalaires UDF. Il s’agit d’une fonctionnalité qui est prise en charge dans la suite de fonctionnalités de traitement intelligent des requêtes dans les bases de données SQL. Cette fonctionnalité améliore les performances des requêtes qui invoquent des fonctions scalaires définies par l'utilisateur dans SQL Server 2019 (15.x) et les versions ultérieures.

Fonctions scalaires définies par l’utilisateur T-SQL

Les fonctions définies par l’utilisateur qui sont implémentées dans Transact-SQL et retournent une valeur de données unique sont appelées fonctions scalaires T-SQL définies par l’utilisateur. Les fonctions UDF T-SQL offrent une façon élégante de réutiliser le code et d’assurer la modularité entre les requêtes Transact-SQL. Certains calculs, tels que des règles métier complexes, sont plus faciles à exprimer sous forme UDF impérative. Les UDF vous aident à créer ce type de logique sans nécessiter d’expertise en rédaction de requêtes SQL. Pour plus d’informations sur les fonctions UDF, voir Créer des fonctions définies par l’utilisateur (moteur de base de données).

Performances des fonctions UDF scalaires

Les fonctions scalaires définies par l’utilisateur offrent généralement de mauvaises performances pour les raisons suivantes :

Appel itératif. Le SQL Moteur de base de données appelle les fonctions définies par l’utilisateur de manière itérative, une fois par tuple admissible. Ce processus ajoute des coûts supplémentaires en raison d’un changement de contexte répété en raison d’un appel de fonction. Les fonctions UDF qui exécutent des requêtes Transact-SQL dans leur définition sont gravement affectées.
Absence d’évaluation des coûts. Pendant l’optimisation, le moteur de la base de données n’évalue le coût que des opérateurs relationnels, et non celui des opérateurs scalaires. Avant l’introduction des fonctions scalaires UDF, les autres opérateurs scalaires étaient généralement peu coûteux et n’exigeaient pas une évaluation des coûts. L’ajout d’un coût processeur réduit pour une opération scalaire suffisait. Il existe des scénarios où le coût réel est significatif, mais l’optimiseur le sous-représente toujours.
Exécution interprétée. Le moteur de base de données évalue les UDF sous forme de lot d’instructions et les exécute instruction par instruction. Chaque instruction est compilée et le plan compilé est mis en cache. Bien que cette stratégie de mise en cache économise un certain temps en évitant les recompilations, chaque instruction s’exécute en isolation. Le moteur de base de données n’effectue aucune optimisation entre les instructions.
Exécution en série. SQL Server n’autorise pas le parallélisme intra-requête dans des requêtes qui appellent des fonctions UDF.

Incorporation automatique des fonctions UDF scalaires

L’objectif de la fonctionnalité d’intégration en ligne des fonctions scalaires définies par l’utilisateur (UDF) est d’améliorer les performances des requêtes qui font appel à des fonctions scalaires définies par l’utilisateur en T-SQL, lorsque l’exécution des UDF constitue le principal goulot d’étranglement.

En utilisant la fonctionnalité d’incorporation de fonctions UDF, le moteur de base de données transforme automatiquement les fonctions scalaires définies par l’utilisateur en expressions scalaires ou en sous-requêtes scalaires. Le moteur de base de données remplace ces expressions ou sous-requêtes dans la requête appelante à la place de l’opérateur UDF. L’optimiseur de requête optimise ensuite ces expressions et sous-requêtes. En conséquence, le plan de requête n’a plus d’opérateur de fonction définie par l’utilisateur, mais ses effets sont désormais observables dans le plan, comme les vues ou les fonctions table (TVF) inline.

Incorporation automatique des fonctions scalaires UDF dans Microsoft Fabric Data Warehouse

Dans Microsoft Fabric Data Warehouse, les fonctions scalaires UDF (actuellement en préversion) sont automatiquement intégrées au moment de la compilation lorsque le corps de la fonction et la requête d'appel répondent aux exigences d'incorporation. Pour plus d’informations, consultez CREATE FUNCTION et Incorporation des fonctions UDF scalaires.

Exemples

Les exemples de cette section utilisent la base de données du benchmark TPC-H. Pour plus d’informations, voir la page d'accueil TPC-H.

A. Fonction scalaire UDF à instruction unique

Regardez la requête qui suit.

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

Cette requête calcule la somme des prix avec remise des articles et présente les résultats groupés par date d’expédition et priorité d’expédition. L’expression L_EXTENDEDPRICE *(1 - L_DISCOUNT) est la formule correspondant au prix avec remise d’un article donné. Il est possible d’extraire ces formules dans des fonctions afin d’assurer leur modularité et leur réutilisation.

CREATE FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
AS
BEGIN
    RETURN @price * (1 - @discount);
END

La requête peut alors être modifiée pour appeler cette fonction UDF.

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

La requête avec la fonction UDF est peu performante, pour les raisons exposées précédemment. Avec l’incorporation des fonctions scalaires UDF, l’expression scalaire figurant dans le corps de la fonction UDF est substituée directement dans la requête. Les résultats de l’exécution de la requête sont affichés dans le tableau suivant :

Query	Requête sans fonction UDF	Requête avec fonction UDF (sans incorporation)	Requête avec incorporation des fonctions UDF scalaires
`Execution time`	1.6 secondes	29 minute 11 secondes	1.6 secondes

Ces nombres sont basés sur une base de données CCI de 10 Go (utilisant le schéma TPC-H) en cours d’exécution sur une machine à biprocesseur (12 cœurs) dotée de 96 Go de RAM et soutenue par un disque SSD. Ces nombres incluent la durée de compilation et d’exécution avec un pool de mémoires tampons et un cache de procédures à froid. La configuration par défaut a été utilisée et aucun autre index n’a été créé.

B. Fonction scalaire UDF à instructions multiples

Vous pouvez également incorporer en ligne des fonctions scalaires définies par l’utilisateur avec plusieurs instructions T-SQL, comme des affectations de variables et des branchements conditionnels. Considérez la fonction UDF scalaire suivante qui, à partir d’une clé client donnée, détermine la catégorie de service pour le client. Il arrive à la catégorie en calculant d’abord le prix total de toutes les commandes passées par le client à l’aide d’une requête SQL. Ensuite, elle utilise une logique IF (...) ELSE pour décider de la catégorie en fonction du prix total.

CREATE OR ALTER FUNCTION dbo.customer_category (@ckey INT)
RETURNS CHAR (10)
AS
BEGIN
    DECLARE @total_price AS DECIMAL (18, 2);
    DECLARE @category AS CHAR (10);
    SELECT @total_price = SUM(O_TOTALPRICE)
    FROM ORDERS
    WHERE O_CUSTKEY = @ckey;
    IF @total_price < 500000
        SET @category = 'REGULAR';
    ELSE
        IF @total_price < 1000000
            SET @category = 'GOLD';
        ELSE
            SET @category = 'PLATINUM';
    RETURN @category;
END

À présent, considérez une requête qui appelle cette fonction UDF.

SELECT C_NAME,
       dbo.customer_category(C_CUSTKEY)
FROM CUSTOMER;

Le plan d’exécution pour cette requête dans SQL Server 2017 (14.x) (niveau de compatibilité 140 ou antérieur) est le suivant :

Comme le montre le plan, SQL Server adopte la stratégie de base suivante : pour chaque tuple de la CUSTOMER table, appelez la fonction UDF et affichez les résultats. Cette stratégie est naïve et inefficace. Avec l’incorporation, ces fonctions UDF sont transformées en sous-requêtes scalaires équivalentes, qui sont substituées dans la requête d’appel à la place de la fonction UDF.

Pour la même requête, le plan avec la fonction UDF incorporée se présente comme suit.

Comme mentionné précédemment, le plan de requête n’a plus d’opérateur de fonction défini par l’utilisateur, mais vous pouvez maintenant voir ses effets dans le plan, comme les vues ou les fichiers TVF inline. Voici quelques observations clés tirées du plan précédent :

SQL Server déduit la jointure implicite entre CUSTOMER et ORDERS et la rend explicite via un opérateur de jointure.
SQL Server déduit également le GROUP BY O_CUSTKEY on ORDERS implicite et utilise IndexSpool + StreamAggregate pour l’implémenter.
SQL Server utilise désormais le parallélisme entre tous les opérateurs.

En fonction de la complexité de la logique dans la fonction UDF, le plan de requête résultant peut également devenir plus grand et plus complexe. Comme vous pouvez le voir, les opérations à l’intérieur de l’UDF ne sont désormais plus opaques, ce qui permet à l’optimiseur de requêtes d’évaluer le coût de ces opérations et de les optimiser. En outre, comme la fonction UDF n’est plus dans le plan, l’appel itératif de la fonction UDF est remplacé par un plan qui permet d’éviter toute surcharge d’appel de fonction.

Exigences des fonctions scalaires UDF incorporables

Une fonction scalaire UDF T-SQL peut être incorporée si la définition de fonction utilise des constructions autorisées et que la fonction est utilisée dans un contexte qui permet l’incorporation :

Toutes les conditions suivantes de la définition UDF doivent être remplies :

La fonction UDF est écrite à l’aide des constructions suivantes :
- DECLARE, SET : Déclaration et affectations des variables.
- SELECT : Requête SQL avec une ou plusieurs affectations de variables ¹.
- IF / ELSE : Création de branches avec des niveaux d’imbrication arbitraires.
- RETURN : Une ou plusieurs instructions return. À partir de SQL Server 2019 (15.x) CU5, la fonction UDF ne peut contenir qu'une seule instruction RETURN pour être prise en compte pour l'incorporation ⁶.
- UDF : Appels de fonction imbriqués/récursifs ².
- Autres : Opérations relationnelles telles que EXISTS, IS NULL.
La fonction UDF n’appelle pas de fonction intrinsèque dépendante du temps (telle que GETDATE()) ou ayant des effets secondaires ³ (telle que NEWSEQUENTIALID()).
La fonction UDF utilise la clause EXECUTE AS CALLER (comportement par défaut si la clause EXECUTE AS n’est pas spécifiée).
La fonction UDF ne référence pas de variables de table ni de paramètres table.
La fonction UDF n’est pas compilée en mode natif (l’interopérabilité est prise en charge).
La fonction UDF ne référence pas de types définis par l’utilisateur.
Aucune signature n’est ajoutée à la fonction UDF ⁹.
La fonction UDF n’est pas une fonction de partition.
La fonction UDF ne contient aucune référence à des expressions de table commune (CTE).
Quand elle est incorporée, la fonction UDF ne contient pas de références à des fonctions intrinsèques qui peuvent modifier les résultats (comme @@ROWCOUNT) ⁴.
La fonction UDF ne contient pas de fonctions d’agrégation transmises comme paramètres à une fonction scalaire UDF ⁴.
La fonction UDF ne référence pas de vues intégrées (comme OBJECT_ID) ⁴.
La fonction UDF ne référence pas de méthodes XML ⁵.
La fonction UDF ne contient pas de SELECT avec ORDER BY sans clause TOP 1⁵.
La fonction UDF ne contient pas de requête SELECT qui effectue une assignation avec la clause ORDER BY (par exemple, SELECT @x = @x + 1 FROM table1 ORDER BY col1) ⁵.
La fonction UDF ne contient pas plusieurs instructions RETURN ⁶.
La fonction UDF ne référence pas la fonction STRING_AGG⁶.
La fonction UDF ne référence pas les tables distantes ⁷.
La fonction UDF ne fait pas référence à des colonnes chiffrées ⁸.
La fonction UDF ne contient pas de références à WITH XMLNAMESPACES⁸.
Si la définition de la fonction UDF s’exécute dans des milliers de lignes de code, SQL Server peut choisir de ne pas l’incorporer.

¹SELECT avec une accumulation/agrégation de variable n’est pas pris en charge pour l’incorporation (par exemple, SELECT @val += col1 FROM table1).

² Les fonctions UDF récursives sont incorporées seulement jusqu’à une certaine profondeur.

³ Les fonctions intrinsèques dont les résultats dépendent de l’heure système actuelle sont dépendantes de l’heure. Une fonction intrinsèque susceptible de mettre à jour un état global interne est un exemple de fonction ayant des effets secondaires. Ces fonctions retournent des résultats différents chaque fois qu’elles sont appelées, selon l’état interne.

⁴ Restriction ajoutée dans SQL Server 2019 (15.x) CU 2

⁵ Restriction ajoutée dans SQL Server 2019 (15.x) CU 4

⁶ Restriction ajoutée dans SQL Server 2019 (15.x) CU 5

⁷ Restriction ajoutée dans SQL Server 2019 (15.x) CU 6

⁸ Restriction ajoutée dans SQL Server 2019 (15.x) CU 11

⁹ Étant donné que les signatures peuvent être ajoutées et supprimées après la création d’une fonction UDF, le choix de l’incorporation se fait lorsque la requête qui référence une fonction UDF est compilée. Par exemple, les fonctions système sont généralement signées avec un certificat. Vous pouvez utiliser sys.crypt_properties pour rechercher les objets signés.

Toutes les exigences suivantes du contexte d’exécution doivent être remplies :

La fonction UDF n’est pas utilisée dans la clause ORDER BY.
La requête qui appelle une fonction scalaire UDF ne référence pas un appel de fonction scalaire UDF dans sa clause GROUP BY.
La requête qui appelle une fonction scalaire UDF dans sa liste de sélection avec la clause DISTINCT n’a pas de clause ORDER BY.
La fonction UDF n’est pas appelée à partir d’une instruction RETURN ¹.
La requête appelant la fonction UDF ne comporte pas d’expressions de table communes (CTE) ³.
La requête d’appel UDF n’utilise pas GROUPING SETS, CUBE ou ROLLUP².
La requête d’appel UDF ne contient pas de variable que vous utilisez comme paramètre UDF pour l’affectation (par exemple, SELECT @y = 2, @x = UDF(@y)) ².
Vous n’utilisez pas UDF dans une colonne calculée ni dans la définition d’une contrainte CHECK.

¹ Restriction ajoutée dans SQL Server 2019 (15.x) CU 5

² Restriction ajoutée dans SQL Server 2019 (15.x) CU 6

³ Restriction ajoutée dans SQL Server 2019 (15.x) CU 11

Pour obtenir des informations sur les derniers correctifs et modifications concernant les scénarios d’éligibilité au inlining des UDF scalaires T-SQL, consultez l’article de la Base de connaissances : CORRECTIF : problèmes de inlining des UDF scalaires dans SQL Server 2019.

Vérifier si une fonction UDF peut être incorporée

Pour chaque fonction scalaire UDF T-SQL, la vue de catalogue sys.sql_modules inclut une propriété appelée is_inlineable, qui indique si une fonction UDF est incorporable.

La propriété is_inlineable est dérivée des constructions trouvées dans la définition UDF. Elle ne vérifie pas si la fonction UDF est incorporable au moment de la compilation. Pour plus d’informations, consultez les conditions d’incorporation.

Une valeur 1 indiquant que l’UDF est incorporable, et 0 indique le contraire. Cette propriété a également la valeur 1 pour toutes les fonctions table incorporées. Pour tous les autres modules, la valeur est 0.

Si une fonction scalaire UDF est incorporable, cela ne signifie pas pour autant qu'elle est toujours incorporée. SQL Server décide (pour chaque requête et chaque fonction UDF) s'il convient d'intégrer une fonction UDF. Reportez-vous aux listes d'exigences figurant plus haut dans cet article.

SELECT b.name,
       b.type_desc,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('IF', 'TF', 'FN');

Vérifier si l’incorporation a eu lieu

Si toutes les conditions préalables sont remplies et que SQL Server décide d’effectuer l’incorporation, il transforme la fonction UDF en une expression relationnelle. À partir du plan de requête, vous pouvez déterminer si l’incorporation a eu lieu :

Le code XML du plan n’a pas de nœud XML <UserDefinedFunction> pour une fonction UDF incorporée correctement.
Certains événements étendus sont émis.

Activer l’incorporation des fonctions scalaires UDF

Vous pouvez rendre les charges de travail automatiquement éligibles à l’incorporation des fonctions UDF scalaires en activant le niveau de compatibilité 150 pour la base de données. Vous pouvez définir cette option à l’aide de Transact-SQL. Par exemple :

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

En dehors de cette étape, aucune autre modification n'est nécessaire au niveau des fonctions UDF ou des requêtes pour pouvoir profiter de cette fonctionnalité.

Désactiver l’incorporation des fonctions scalaires UDF sans modifier le niveau de compatibilité

Vous pouvez désactiver l’incorporation de fonctions UDF scalaires au niveau de la base de données, de l’instruction ou de l’étendue UDF tout en conservant le niveau de compatibilité de la base de données 150 et ultérieur. Pour désactiver l’incorporation en ligne des fonctions UDF scalaires au niveau de la base de données, exécutez l’instruction suivante dans le contexte de la base de données concernée :

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Pour réactiver l’incorporation de fonctions UDF scalaires pour la base de données, exécutez l’instruction suivante dans le contexte de la base de données applicable :

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Lorsque vous définissez cette option ONsur , elle apparaît comme activée dans sys.database_scoped_configurations.

Vous pouvez également désactiver l’incorporation des fonctions UDF scalaires pour une requête spécifique en désignant DISABLE_TSQL_SCALAR_UDF_INLINING comme indicateur de requête USE HINT.

Un indicateur de requête USE HINT est prioritaire par rapport à la configuration étendue à la base de données et par rapport à un paramètre de niveau de compatibilité.

Par exemple :

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

Vous pouvez également désactiver l’incorporation des UDF scalaires pour une UDF spécifique à l’aide de la clause INLINE dans l’instruction CREATE FUNCTION ou ALTER FUNCTION. Par exemple :

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * (1 - @discount);
END

Après avoir exécuté l’instruction précédente, cette fonction définie par l’utilisateur (UDF) n’est jamais développée en ligne dans aucune requête qui l’invoque. Pour réactiver l’inlining pour cette UDF, exécutez l’instruction suivante :

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * (1 - @discount);
END

La clause INLINE n’est pas obligatoire. Si vous ne spécifiez pas la clause INLINE, elle prend automatiquement la valeur ON ou OFF selon que la fonction UDF peut être incorporée en ligne ou non. Si INLINE = ON est spécifié mais que la fonction UDF s’avère inéligible pour l’incorporation, une erreur est levée.

Remarques

Comme cela est décrit dans cet article, l’incorporation des fonctions UDF scalaires transforme une requête avec des fonctions UDF scalaires en une requête avec une sous-requête scalaire équivalente. En raison de cette transformation, les utilisateurs peuvent remarquer des différences de comportement dans les scénarios suivants :

L’incorporation génère un hachage de requête différent pour le même texte de requête.
Certains avertissements dans les instructions de la fonction UDF (tels qu’une division par zéro, etc.) qui pouvaient auparavant être masqués peuvent apparaître en raison de l'incorporation.
Les indicateurs de jointure au niveau des requêtes ne sont peut-être plus valides, car l’incorporation peut introduire de nouvelles jointures. Vous devez utiliser des indicateurs de jointure locaux à la place.
Vous ne pouvez pas indexer les vues qui référencent des UDF scalaires inline. Si vous avez besoin de créer un index sur ces vues, désactivez l’incorporation pour les fonctions UDF référencées.
Il peut y avoir des différences dans le comportement du masquage des données dynamiques avec l’incorporation des fonctions UDF.

Dans certaines situations (en fonction de la logique de la fonction UDF), l’incorporation peut être plus conservatrice en ce qui concerne le masquage des colonnes de sortie. Dans les scénarios où les colonnes référencées dans une fonction UDF ne sont pas les colonnes de sortie, elles ne sont pas masquées.
Si une fonction UDF référence des fonctions intégrées telles que SCOPE_IDENTITY(), @@ROWCOUNT ou @@ERROR, la valeur retournée par la fonction intégrée change avec l’incorporation. Ce changement de comportement est dû au fait que l’incorporation modifie l’étendue des instructions au sein de la fonction UDF. À partir de SQL Server 2019 (15.x) CU2, l’incorporation est bloquée si la fonction UDF référence certaines fonctions intrinsèques (par exemple @@ROWCOUNT).
Si vous affectez une variable avec le résultat d’une fonction UDF inline et que vous l’utilisez également comme index_column_name indicateur de FORCESEEKrequête (Transact-SQL), elle génère l’erreur 8622. Cette erreur indique que le processeur de requêtes n’a pas pu produire de plan de requête en raison des indicateurs définis dans la requête.

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2026-06-30