Inlining des fonctions à valeur de table dans SQL Server

Les fonctions à valeur de table dans SQL Server sont excellentes pour écrire du code SQL DRY en encapsulant des bribes de logique de base de données couramment utilisées. Cependant, dans certains cas, elles peuvent être à l’origine de graves problèmes de performances. Prenons un exemple. Disons que nous avons une table qui ressemble à ce qui suit.

CREATE TABLE Products(ProductId INT,VendorId INT,Description NVARCHAR(1000))

Imaginez maintenant que dans le contexte d’une procédure stockée, nous avons la logique récurrente suivante.

SELECT *FROM ProductsWHERE Description LIKE @Description

Nous pouvons vouloir refacturer ceci en une fonction pour rendre notre procédure stockée plus lisible et plus facile à refacturer. De nombreux tutoriels sur internet vous diront de créer une fonction à valeur de table comme suit.

CREATE FUNCTION FN_Products_GetByDescription (@description NVARCHAR(1000))RETURNS @results TABLE(ProductId INT,VendorId INT,Description NVARCHAR(1000))ASBEGIN;INSERT INTO @resultsSELECT *FROM ProductsWHERE Description LIKE @description;RETURN;END;

Le problème avec ce type de fonction est qu’elle agit comme une boîte noire pour le code qui l’appelle. Imaginez que nous appelions notre nouvelle fonction dans le scénario suivant.

SELECT *FROM FN_Products_GetByDescription('%book%')WHERE VendorId = 3;

Pour exécuter ceci, SQL Server doit parcourir chaque produit de notre base de données, en vérifiant la description du produit par rapport à notre texte de requête, avant de renvoyer cet ensemble de résultats au code appelant qui les filtre ensuite par VendorId. Dans les grandes bases de données, cela est clairement très inefficace. Ne serait-il pas préférable de faire savoir à SQL Server dès le départ que nous voulons filtrer sur le texte de notre requête ainsi que sur le VendorId ? De cette façon, il pourrait d’abord filtrer par VendorId, puis exécuter le coûteux filtrage LIKE sur un ensemble de résultats plus petit. C’est là que l’inlining de fonction entre en jeu. Pour inliner notre fonction, il suffit de la réécrire comme suit.

CREATE FUNCTION FN_Products_GetByDescription (@description NVARCHAR(1000))RETURNS TABLEASRETURN(SELECT *FROM ProductsWHERE Description LIKE @description);

Maintenant, lorsque SQL Server exécute la requête ci-dessus, la fonction est en coulisse traitée comme une partie du code appelant, au lieu d’une boîte noire qui doit renvoyer une valeur avant d’aller plus loin. Cela permet à l’optimiseur de requêtes d’être beaucoup plus efficace pour optimiser les requêtes, parce qu’il sait dès le départ exactement quelles sont les données dont nous avons besoin.

Alors, quand ne voudrions-nous jamais ne pas inliner les fonctions à valeur de table ? Eh bien, les fonctions ne peuvent être inlines que lorsqu’elles consistent en une seule instruction SELECT qui renvoie un ensemble de résultats. Les fonctions qui contiennent plusieurs sous-requêtes qui remplissent un tableau de résultats, ainsi que celles qui contiennent des instructions supplémentaires telles que les IF ne sont pas inlineables. Cependant, si vous pouvez rendre une requête inline, soit directement, soit en la réécrivant légèrement, c’est généralement une très bonne idée. Les gains de performance que vous pouvez obtenir de l’inlining des fonctions peuvent être spectaculaires, et vous offrent la flexibilité et la lisibilité d’un code DRY, sans un grand coût de performance.