Incorporation des fonctions UDF scalaires

Article
08/23/2023

S’applique à : SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance

Cet article présente l’incorporation (inlining) des fonctions UDF scalaires. Il s’agit d’une fonctionnalité qui est prise en charge dans la suite de fonctionnalités de traitement intelligent des requêtes. Cette fonctionnalité améliore les performances des requêtes qui appellent des fonctions définies par l’utilisateur scalaire dans SQL Server (à compter de SQL Server 2019 (15.x)).

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

Les fonctions définies par l’utilisateur (UDF) qui sont implémentées dans Transact-SQL et retournent une valeur de données unique sont appelées fonctions scalaires scalaires T-SQL définies par l’utilisateur. Les fonctions définies par l’utilisateur T-SQL constituent un moyen élégant d’obtenir la réutilisation et la modularité du code dans les requêtes Transact-SQL. Certains calculs (tels que des règles métier complexes) sont plus faciles à exprimer sous forme de fonctions UDF impératives. Les fonctions UDF favorisent la création d’une logique complexe sans devoir savoir écrire des requêtes SQL complexes. Pour plus d’informations sur les fonctions UDF, consultez Créer des fonctions définies par l’utilisateur (moteur de base de données).

Performances des fonctions UDF scalaires

Les fonctions UDF scalaires présentent généralement des performances médiocres pour les raisons suivantes :

Appel itératif. les fonctions UDF sont appelées de façon itérative, une fois par tuple éligible. Cela implique des coûts supplémentaires de changements de contexte répétés en raison de l’appel de fonction. En particulier, les fonctions définies par l’utilisateur qui exécutent des requêtes Transact-SQL dans leur définition sont gravement affectées.
Manque de coût. Pendant l’optimisation, seuls les opérateurs relationnels sont facturés, tandis que les opérateurs scalaires ne le sont pas. Avant l’introduction de fonctions définies par l’utilisateur scalaire, d’autres opérateurs scalaires étaient généralement bon marché et n’avaient pas besoin de 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 important et reste pourtant sous-représenté.
Exécution interprétée. les fonctions UDF sont évaluées sous la forme d’un lot d’instructions, exécuté instruction par instruction. Chaque instruction proprement dite est compilée et le plan compilé est mis en cache. Cette stratégie de mise en cache permet d’économiser du temps, car elle évite les recompilations, mais chaque instruction s’exécute de manière isolée. Aucune optimisation entre les instructions n’est réalisée.
Exécution en série. SQL Server n’autorise pas le parallélisme intra-requête dans les requêtes qui appellent des fonctions définies par l’utilisateur.

Incorporation automatique des fonctions UDF scalaires

L’objectif de la fonctionnalité d’inlining UDF Scalar est d’améliorer les performances des requêtes qui appellent des fonctions définies par l’utilisateur scalaire T-SQL, où l’exécution UDF est le goulot d’étranglement principal.

Avec cette nouvelle fonctionnalité, les fonctions UDF scalaires sont automatiquement transformées en expressions scalaires ou sous-requêtes scalaires qui sont substituées dans la requête d’appel à la place de l’opérateur UDF. Ces expressions et sous-requêtes sont ensuite optimisées. Par conséquent, le plan de requête n’a plus d’opérateur de fonction définie par l’utilisateur, mais ses effets sont observés dans le plan, tels que des vues ou des fonctions table (TVF) inline.

Exemple 1 - UDF scalaire d’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

Pour les raisons décrites précédemment, la requête avec la fonction UDF présente des performances médiocres. Maintenant, avec l’inlining UDF Scalar, l’expression scalaire dans le corps de la fonction UDF est remplacée directement dans la requête. Les résultats de l’exécution de la requête sont affichés dans le tableau ci-dessous :

Requête :	Requête sans fonction UDF	Requête avec fonction UDF (sans incorporation)	Requête avec inlining UDF Scalar
Durée d’exécution :	1,6 seconde	29 minutes et 11 secondes	1,6 seconde

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éé.

Exemple 2 - UDF scalaire à plusieurs instructions

Les fonctions UDF scalaires qui sont implémentées à l’aide de plusieurs instructions T-SQL, telles que les affectations de variables et le branchement conditionnel, peuvent également être incorporées. 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. Elle arrive à cette catégorie en calculant au départ 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 DECIMAL(18,2);
  DECLARE @category 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 de cette requête dans SQL Server 2017 (14.x) (niveau de compatibilité 140 et versions antérieures) est le suivant :

Query Plan without inlining.

Comme le montre le plan, SQL Server adopte une stratégie simple ici : appeler la fonction UDF et fournir les résultats pour chaque tuple figurant dans la table CUSTOMER. 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.

Query Plan with inlining.

Comme mentionné précédemment, 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, tels que des vues ou des fonctions table (TVF) inline. Voici quelques observations clés tirées du plan ci-dessus :

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

Selon la complexité de la logique dans la fonction UDF, le plan de requête obtenu peut également grandir et se complexifier. Comme nous pouvons le voir, les opérations à l’intérieur de l’UDF ne sont plus opaques, et l’optimiseur de requête est donc en mesure de coûter et d’optimiser ces opérations. 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 UDF scalaires inlineables

Une fonction UDF T-SQL scalaire peut être incorporée si toutes les conditions suivantes sont remplies :

La fonction UDF est écrite à l’aide des constructions suivantes :
- DECLARE, SET : Déclaration et affectations des variables.
- SELECT: requête SQL avec affectations de variables uniques/multiples ¹.
- IF/ELSE : Création de branches avec des niveaux d’imbrication arbitraires.
- RETURN : Une ou plusieurs instructions return. À compter de SQL Server 2019 (15.x) CU5, la fonction UDF ne peut contenir qu’une seule instruction RETURN à prendre en compte pour l’incorporation ⁶.
- UDF: fonction imbriquée/récursive appelle ².
- Autres : Opérations relationnelles telles que EXISTS, IS NULL.
L’UDF n’appelle aucune fonction intrinsèque dépendante (par exemple GETDATE()) ou a des effets ^{secondaires 3} (par NEWSEQUENTIALID()exemple).
La fonction UDF utilise la EXECUTE AS CALLER clause (comportement par défaut si la EXECUTE AS clause n’est pas spécifiée).
La fonction UDF ne référence pas les variables de table ni les paramètres table.
La requête appelant une fonction UDF scalaire ne fait pas référence à un appel UDF scalaire dans sa GROUP BY clause.
La requête appelant une fonction UDF scalaire dans sa liste de sélection avec DISTINCT clause n’a ORDER BY pas de clause.
La fonction UDF n’est pas utilisée dans la ORDER BY clause.
La fonction UDF n’est pas compilée en mode natif (l’interopérabilité est prise en charge).
La fonction UDF n’est pas utilisée dans une colonne calculée ou dans une définition de contrainte case activée.
La fonction UDF ne référence pas les 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 pas de références aux expressions de table communes (CTEs).
La fonction UDF ne contient pas de références à des fonctions intrinsèques qui peuvent modifier les résultats lorsqu’elles sont insérées (par @@ROWCOUNTexemple) ⁴.
La fonction UDF ne contient pas de fonctions d’agrégation passées en tant que paramètres à une fonction UDF ^{scalaire 4}.
La fonction UDF ne fait pas référence à des vues intégrées (telles que OBJECT_ID) ⁴.
La fonction UDF ne référence pas les méthodes ^{XML 5}.
La fonction UDF ne contient pas de SELECT avec ORDER BY sans TOP 1 clause ⁵.
La fonction UDF ne contient pas de requête SELECT qui effectue une affectation avec la ORDER BY clause (par exemple SELECT @x = @x + 1 FROM table1 ORDER BY col1) ⁵.
La fonction UDF ne contient pas plusieurs instructions ^{RETURN 6}.
La fonction UDF n’est pas appelée à partir d’une instruction ^{RETURN 6}.
La fonction UDF ne fait pas référence à la STRING_AGG fonction ⁶.
La fonction UDF ne référence pas les tables distantes ⁷.
La requête d’appel UDF n’utilise GROUPING SETSpas , CUBEou ROLLUP⁷.
La requête d’appel UDF ne contient pas de variable utilisée comme paramètre UDF pour l’affectation (par exemple, SELECT @y = 2, @x = UDF(@y)) ⁷.
La fonction UDF ne référence pas les colonnes ^{chiffrées 8}.
La fonction UDF ne contient pas de références à WITH XMLNAMESPACES⁸.
La requête appelant la fonction UDF n’a pas d’expressions de table courantes (CTEs) ⁸.

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

² 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 qui peut mettre à jour un état global interne est un exemple de fonction avec effets secondaires. Ces fonctions retournent des résultats différents chaque fois qu’elles sont appelées, en fonction de l’état interne.

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

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

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

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

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

Pour plus d’informations sur les derniers correctifs DDF scalaires T-SQL et les modifications apportées aux scénarios d’éligibilité à l’incorporation, consultez l’article de la Base de connaissances : CORRECTIF : Problèmes d’inlining UDF Scalar dans SQL Server 2019.

Vérifier si une fonction UDF peut être insérée ou non

Pour chaque fonction UDF scalaire 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 ou non.

La propriété is_inlineable est dérivée des constructions trouvées dans la définition UDF. Elle n’case activée pas si l’UDF est en fait inlineable au moment de la compilation. Pour plus d’informations, consultez les conditions d’incorporation.

La valeur 1 indique qu’elle est incorporable, et 0 indique le contraire. Cette propriété a également la valeur 1 pour toutes les fonctions table inline. Pour tous les autres modules, la valeur est 0.

Si une fonction UDF scalaire est inlineable, cela n’implique pas qu’elle soit toujours inline. SQL Server décide (pour chaque requête et chaque fonction UDF) s’il convient d’incorporer une fonction UDF ou non. Voici quelques exemples de cas où une fonction UDF peut ne pas être incorporée :

Si la définition UDF s’exécute dans des milliers de lignes de code, SQL Server peut choisir de ne pas l’insérer.
Un appel UDF dans une GROUP BY clause ne sera pas incorporé. Cette décision est prise lorsque la requête qui référence une fonction UDF scalaire est compilée.
Si la fonction UDF est signée avec un certificat. Étant donné que les signatures peuvent être ajoutées et supprimées après la création d’une fonction UDF, la décision d’inline ou non est effectuée lorsque la requête référençant une fonction UDF scalaire 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.
```
SELECT *
FROM sys.crypt_properties AS cp
INNER JOIN sys.objects AS o ON cp.major_id = o.object_id;
```

Vérifier si l’inlining s’est produit ou non

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, il est facile de déterminer si l’incorporation s’est produite ou non :

Le plan xml n’a pas de <UserDefinedFunction> nœud xml pour une fonction UDF qui a été insérée avec succès.
Certains événements XEvents sont émis.

Activer l’inlining UDF Scalar

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 outre, aucune autre modification des requêtes ou des fonctions UDF n’est requise pour tirer parti de cette fonctionnalité.

Désactiver l’inlining UDF Scalar sans modifier le niveau de compatibilité

L’inlining UDF scalaire peut être désactivée 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’inlining UDF Scalar dans l’étendue de 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 = OFF;

Pour réactiver l’incorporation des 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 l'option est activée (ON), ce paramètre apparaît activé 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'));

L’incorporation des fonctions UDF scalaires peut également être désactivée pour une fonction 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;

Une fois l’instruction ci-dessus exécutée, cette fonction UDF ne sera jamais incorporée dans aucune requête qui l’appelle. Pour réactiver l’incorporation pour cette fonction 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 INLINE clause n’est pas obligatoire. Si INLINE la clause n’est pas spécifiée, elle est automatiquement définie ON/OFF sur la base de la possibilité d’inliner la fonction UDF. 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 importantes

Comme décrit dans cet article, l’inlining UDF Scalar transforme une requête avec des fonctions définies par l’utilisateur scalaire 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 figurant dans la fonction UDF (tels qu’une division par zéro, etc.), qui peuvent avoir été cachés précédemment, 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. Les indicateurs de jointure locaux doivent être utilisés à la place.
Les vues qui font référence aux fonctions définies par l’utilisateur scalaire inline ne peuvent pas être indexées. 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 de comportement de Dynamic Data Masking avec l’incorporation des données UDF.

Dans certaines situations (selon la logique de l’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 des colonnes de sortie, elles ne seront 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. À compter de SQL Server 2019 (15.x) CU2, l’incorporation est bloquée si l’UDF fait référence à certaines fonctions intrinsèques (par exemple @@ROWCOUNT).
Si une variable est affectée avec le résultat d’une fonction UDF inline et qu’elle est également utilisée comme index_column_name dans l’indicateur de requête FORCESEEK, l’erreur Msg 8622 indique que le processeur de requêtes n’a pas pu produire un plan de requête en raison des indicateurs définis dans la requête.