Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

SQL Server : Fonctions scalaires, fausse bonne idée ?

Comme vous le savez peut être SQL Server supporte la notion de fonctions depuis sa version 2000. Cependant cette fonctionnalité très attendus à quelques limites à connaitre. Je ne parlerais dans ce message que des fonctions scalaires qui renvoie une seule valeur d'un type donné.

Tout d'abord sachez qu'il n'est pas possible d'inclure n'importe quoi dans une fonction. Sont interdit :

  • Les fonctions non déterministes (fonctions ne renvoyant pas la même valeur avec les même arguments), même si certaines sont possible sous SQL Server 2005 uniquement (GETDATE() par exemple)
  • Les modifications durables du système ou des données (y compris dans des tables temporaires)

Pour ces 2 points il est possible de passer outre d'une manière ou d'une autre :

-- Code interdit

CREATE FUNCTION dbo.yyy ()
RETURNS uniqueidentifier
BEGIN 
    RETURN NEWID()
END

Msg 443, Level 16, State 1, Procedure yyy, Line 6
Invalid use of side-effecting or time-dependent operator in 'newid' within a function.

-- Code autorisé
CREATE VIEW MaVue
AS 
    SELECT NEWID() As MonId
GO

CREATE FUNCTION dbo.yyy ()
  RETURNS uniqueidentifier
BEGIN

    RETURN (SELECT MonId FROM MaVue)

END

Pour le second point il s'agira de passer par des variables de type table qui sont autorisée contrairement aux tables temporaires.

Un autre aspect important concerne l'exécution des fonctions, comparons :

-- Fonction volontairement très simple
CREATE FUNCTION dbo.xxx (@a int, @b int
   RETURNS int
BEGIN

    RETURN @a + @b

END

-- Sans fonction
SELECT [AddressID] + [StateProvinceID]
FROM [AdventureWorks].[Person].[Address]

-- Avec fonction
SELECT dbo.xxx([AddressID], [StateProvinceID])
FROM [AdventureWorks].[Person].[Address]

Nous avons 2 requêtes, une avec fonction, l'autre sans, voici les résultats de leur exécutions :

  • Sans fonction : 15 ms de temps CPU, 30 I/O
  • Avec fonction : 780 ms de temps CPU, 30 I/O

La différence est énorme entre les 2, d'où vient elle ? Nous allons le voir avec le Profiler SQL Server.

On démarre SQL Profiler, on se connecte au serveur, le modèle à choisir est « blank »/ « Vide ». Dans l'onglet « Events selection » / « Selection d'évènements » on choisira : « Stored Procedures » / « Procédures Stockées » puis « SP :Completed » / « SP : Terminée ».

Au besoin dans les filtres de colonnes, indiquez la base de données ou le numéro de la session sur laquelle vous faites les tests.

On obtient :

SP:Completed    SELECT dbo.xxx([AddressID], [StateProvinceID]) FROM [AdventureWorks].[Person].[Address]
SP:Completed    SELECT dbo.xxx([AddressID], [StateProvinceID]) FROM [AdventureWorks].[Person].[Address]
SP:Completed    SELECT dbo.xxx([AddressID], [StateProvinceID]) FROM [AdventureWorks].[Person].[Address]

SP:Completed    SELECT dbo.xxx([AddressID], [StateProvinceID]) FROM [AdventureWorks].[Person].[Address]

Autant de fois qu'il y a d'enregistrements dans la table sur laquelle on exécute la requête pour la requête avec fonction. Ce qui n'est pas le cas de la requête sans fonction.

Logiquement lorsque que l'on force une logique itérative dans SQL Server, les performances chutent très significativement. Attention donc à l'utilisation de fonctions scalaires dans votre code T-SQL cela peut conduire à des problèmes de performances sassez important, voici donc quelques conseils :

  • Ne vous servez des fonctions scalaires que pour simplifier la lisibilité d'une requête
  • Vérifiez les appels à la fonction via SQL Profiler avec SP :Completed
  • Ne l'utilisez que sur un faible nombre d'enregistrements, idéalement dans un SELECT, surtout pas dans un WHERE
  • Si le code la fonction est très complexe avec une logique itérative complexe, pourquoi ne pas penser à une fonction SQLCLR (avec .net)

Il y a quand même une bonne nouvelle avec les fonctions, elles sont compilées tout comme les procédures stockées :

SELECT usecounts, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

Renvoie après 8 exécutions de la requête avec fonction.

usecounts

text

8

CREATE FUNCTION dbo.xxx (@a int, @b int) RETURNS int BEGIN RETURN @a + @b END

Les fonctions sont donc compilées comme le sont les procédures stockées, leur plan est conservé en cache et est réutilisé quand la fonction est à nouveau appelée.

Dernier point au niveau de l'optimisation des fonctions :

-- Fonction volontairement très simple
-- Avec une clause WITH RETURNS NULL ON NULL INPUT
CREATE FUNCTION dbo.xxx (@a int, @b int)
  RETURNS int
WITH RETURNS NULL ON NULL INPUT
BEGIN

    RETURN @a + @b

END

-- Avec fonction
SELECT dbo.xxx(NULL, 1)
FROM [AdventureWorks].[Person].[Address]
GO

Résultat 16 ms secondes de CPU, la fonction n'est en fait pas appelée si l'un des arguments passés est à NULL. Ce qui permet une grande amélioration dans ce cas précis.

Bon tests…

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Publié mardi 16 janvier 2007 18:33 par christian
Classé sous : ,

Commentaires

Pas de commentaires
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01