Scribnasium

Vous avez peut-être entendu dire que les fonctions utilisateur définies de SQL Server sont le diable. C’est parce que quelqu’un s’est brûlé. Ils ont mis une fonction scalaire dans une clause WHERE sur une grande table et lui ont donné un nom de colonne. Et de mauvaises choses se sont produites.

Dans ce cas, « mauvais » signifie lent. Vraiment lent.

Sarga-What-Now

Pour que les requêtes soient performantes, SQL Server doit pouvoir trouver les données rapidement, et cela signifie généralement utiliser un index. Un index est une liste de données clés d’une table, organisée pour rendre la recherche plus rapide. Tout comme dans un livre, au lieu de parcourir chaque page pour trouver la mention d’un mot, vous pouvez utiliser l’index, où l’auteur – ou son programme informatique – a fait ce travail pour vous comme une référence pratique.

Un index est plus rapide lorsque vous pouvez l’utiliser directement. Si vous cherchez des pages qui mentionnent « SQL », vous trouvez « SQL » dans l’index et il vous indique les numéros de page à vérifier. Mais que se passe-t-il si vous cherchez tous les mots qui se terminent par « SQL », comme « T-SQL » ? Maintenant, vous ne pouvez pas aller directement à la partie de l’index que vous voulez. Vous devez parcourir l’ensemble de l’index. Vous n’avez plus un seul argument de recherche (« SQL ») ; vous avez une expression de recherche (« se termine par SQL »). Le terme industriel que nous utilisons est que votre recherche n’est plus « sargable » (« Search ARGument ABLE »).

La morale de l’histoire est que si vous voulez qu’un index soit utilisé le plus efficacement possible, assurez-vous que vous recherchez exactement ce qui est indexé. Si vous effectuez une recherche par date, et que vous voulez trouver tout ce qui se trouve dans une certaine année, n’utilisez pas WHERE Year() = 2018". Utilisez plutôt WHERE >= '20180101' AND . As a human, you can understand these as the same thing, but often the optimizer just sees "arbitrary function on indexed column" and figures it can't use the index the normal way but instead has to slog through the whole thing and run the function to see what it does.

Et si l’optimiseur a ce problème avec la fonction intégrée « Year », que pensez-vous qu’il va faire avec votre scalaire « Complex_2000_Line_Monstrosity » ? Il va le traiter comme la boîte noire qu’il est et passer en revue chaque entrée de l’index pour le tester. Et cela suppose qu’il y ait un index. Une grande fonction définie par l’utilisateur a souvent besoin de plusieurs colonnes différentes de données, dont certaines ne sont pas dans un index. Donc maintenant, vous parcourez toute la table. Prends une tasse de café. Vous y resterez un moment.

Vous pouvez voir à travers elles.

Alors que les fonctions scalaires sont opaques pour l’optimiseur, les fonctions inline sont transparentes. Au lieu de voir une fonction inline comme une boîte noire, l’optimiseur de requêtes la voit plutôt comme une vue paramétrée – c’est-à-dire une vue qui se trouve prendre des paramètres. Les vues et les fonctions inline sont essentiellement copiées et collées dans la requête qui les appelle, comme si elles n’étaient pas du tout une construction distincte.

Maintenant, ce n’est pas parce que les limites de votre fonction s’effacent au pays des optimiseurs que vous trouverez votre quête. Vous n’avez pas besoin d’une fonction pour détruire la saragabilité. Le simple réarrangement de votre logique de critères peut gérer cela tout seul. Par exemple, vous ne tirerez pas profit de votre index sur Column1 si vous utilisez WHERE 1 = CASE WHEN Column1 = @value THEN 1 ELSE 0 END au lieu de WHERE Column1 = @value. Encore une fois, nous, les humains, pouvons voir que c’est la même chose, mais pas SQL Server.

Mais si vous utilisez une fonction en ligne qui agit directement sur un champ indexé, sans calcul obfusqué, l’optimiseur utilisera quand même une recherche indexée. Juste comment faire pour que cela se produise sent un peu plus l’art que la science.

Pudding

Considérons une table Orders avec une OrderID colonne, une OrderStatusID colonne et une OrderDate colonne. Les OrderID et OrderDate ne sont pas pertinentes pour nos exemples, sauf pour fournir quelques données supplémentaires liées à la commande. La colonne clé ici est OrderStatusID. Disons que nous avons plusieurs statuts dans lesquels une commande pourrait se trouver (Nouveau, Traitement, Expédition, Payé, etc.).

Maintenant, disons que nous avons une règle de gestion qui ne s’applique que dans certains statuts. Peut-être qu’une commande n’est traitée par un certain service que si elle est Nouvelle ou En cours de traitement, mais pas lorsqu’elle est dans un autre statut. Si vous mettez cette logique métier dans la base de données – comme vous le devriez – votre code de base de données devra comprendre quels statuts sont spéciaux.

La façon la plus efficace en termes de performance d’interroger la table Commandes pour les commandes à statut spécial est de les lister directement dans chaque requête pertinente :

SELECTo.*FROM#Orders oWHEREo.OrderStatusID IN (3,8);

Avec un index sur le champ OrderStatusID, l’optimiseur utiliserait probablement une action de recherche d’index et trouverait vos lignes sans problème. Cependant, vous aurez probablement besoin que la base de données comprenne ce concept à de nombreux endroits différents. Plutôt que de répéter cette liste de « statuts spéciaux », vous décidez que vous voulez une fonction pour calculer si un statut est spécial. De cette façon, votre logique métier est centralisée:

CREATE FUNCTION IsOrderStatusSpecialScalar(@OrderStatusID int)RETURNS bit ASBEGINRETURN(CASEWHEN @OrderStatusID IN (3,8) THEN1ELSE0END);

Maintenant, chaque fois que vous voulez trouver ces statuts spéciaux, vous utilisez simplement la fonction.

SELECTo.*FROM#Orders oWHEREdbo.IsOrderStatusSpecialScalar(o.OrderStatusID) = 1;

Alas, vous trouvez un scan d’index lent parce que l’optimiseur ne sait pas ce qui se passe à l’intérieur de la fonction. Il l’appelle simplement encore et encore pour chaque ligne.

Vous pourriez penser que vous pouvez sauver vos performances en utilisant une fonction inline.

CREATE FUNCTION IsOrderStatusSpecial(@OrderStatusID int)RETURNS TABLE ASRETURNSELECTOrderStatusIsSpecial =CASEWHEN @OrderStatusID IN (3,8) THEN1ELSE0END;

Vous devez changer un peu l’utilisation mais c’est similairement concis.

SELECTo.*FROM#Orders oCROSS APPLYIsOrderStatusSpecial(o.OrderStatusID) sWHEREs.OrderStatusIsSpecial = 1;

Malheureusement, vous rencontrez le même problème de performance de balayage d’index. Vous ne pouvez pas vraiment blâmer la fonction inline, cependant. Après tout, vous obtenez les mêmes mauvaises performances en utilisant directement la logique CASEWHEN de la fonction :

SELECTo.*FROM#Orders oWHERE1 = CASEWHEN o.OrderStatusID IN (3,8) THEN1ELSE0END;

Donc vous devez trouver un moyen d’utiliser une fonction inline où l’optimiseur voit une utilisation plus directe du champ indexé. Vous créez donc une fonction inline qui n’utilise aucun calcul pour produire son bit.

CREATE FUNCTION IsOrderStatusSpecialLimiter(@OrderStatusID int)RETURNS TABLE ASRETURNSELECTOrderIsSpecial = CONVERT(bit,1)WHERE@OrderStatusID IN (3,8);

SELECTo.*FROM#Orders oCROSS APPLYIsOrderStatusSpecialLimiter(o.OrderStatusID) s;

CREATE FUNCTION GetSpecialOrderStatusIDs()RETURNS TABLE ASRETURNSELECTs.OrderStatusIDFROM(SELECT3UNION ALLSELECT8) AS s(OrderStatusID);

SELECTo.*FROM#Orders oWHEREEXISTS(SELECT*FROMGetSpecialOrderStatusIDs() sWHEREs.OrderStatusID = o.OrderStatusID);