Performance des UDF – traitement de chaines

À travers deux exemples, nous allons découvrir comment le code d’une même fonctionnalité peut changer les performances du tout au tout. Nous en tirerons les conclusions qui s’imposent.

Notre première fonction intitulée « F_REMOVE_UNPRINTABLE_CHARS » doit retirer tous les caractères non imprimable et les transformer en blanc.
Nous avons réalisé deux versions de cette première UDF.

REMOVE_UNPRINTABLE_CHARS Version 1 – utilisation de la fonction TRANSLATE

CREATE OR ALTER FUNCTION F_REMOVE_UNPRINTABLE_CHARS_V1(@STRING NVARCHAR(max))
RETURNS NVARCHAR(max)
AS
BEGIN
   DECLARE @UNPRINTABLE_CHARS NCHAR(32) = N'', @I TINYINT = 0;
   WHILE @I < 31
   BEGIN
      SET @UNPRINTABLE_CHARS = @UNPRINTABLE_CHARS + NCHAR(@I);
      SET @I = @I + 1;
   END
   RETURN TRANSLATE (@STRING, @UNPRINTABLE_CHARS, '                                ');
END;
GO

REMOVE_UNPRINTABLE_CHARS Version 2 – utilisation d’une boucle de lecture des caractères

CREATE OR ALTER FUNCTION F_REMOVE_UNPRINTABLE_CHARS_V2(@STRING NVARCHAR(max))
RETURNS NVARCHAR(max)
AS
BEGIN
   DECLARE @STR_OUT NVARCHAR(MAX) = N'', @I INT = 1, @C NCHAR(1);
   WHILE @I < LEN(@STRING)
   BEGIN
      SET @C = SUBSTRING(@STRING, @I, 1);
      SET @STR_OUT = @STR_OUT + CASE WHEN UNICODE(@C) < 32 THEN N' ' ELSE @C END;
      SET @I = @I + 1;
   END
   RETURN @STR_OUT
END;
GO

Un test basique nous permet de savoir si ces fonctions renvoient bien le résultat attendu (la chaine de caractère contient une tabulation) :

-- test basique :
SELECT dbo.F_REMOVE_UNPRINTABLE_CHARS_V1('toto	tata
titi');
SELECT dbo.F_REMOVE_UNPRINTABLE_CHARS_V2('toto	tata
titi');
GO

Nous avons lancé ces fonctions sur la table système sys.sql_modules qui contient le code des routines SQL d’une base comptant plus d’un millier d’objets. En voici les résultats :

-- tests sur bonne volumétrie
SET STATISTICS TIME ON;
GO
SELECT dbo.F_REMOVE_UNPRINTABLE_CHARS_V1(definition)
FROM   sys.sql_modules;
--> Temps UC = 22 922 ms, temps écoulé = 23 456 ms

SELECT dbo.F_REMOVE_UNPRINTABLE_CHARS_V2(definition)
FROM   sys.sql_modules;
--> Temps UC = 92 157 ms, temps écoulé = 101 042 ms.
GO

Notre seconde fonction intitulée « F_REMOVE_DUPLICATE_SPACES » doit retirer tous les caractères blancs superflus en n’en conserver qu’un seul, là ou il y en a plusieurs.
Nous avons aussi réalisé deux versions de cette seconde UDF.

REMOVE_DUPLICATE_SPACES Version 1 – utilisation de la fonction REPLACE

CREATE OR ALTER FUNCTION F_REMOVE_DUPLICATE_SPACES_V1(@STRING NVARCHAR(max))
RETURNS NVARCHAR(max)
AS
BEGIN
   WHILE CHARINDEX('  ', @STRING) > 0
      SET @STRING = REPLACE(@STRING, '  ', ' ');
   RETURN @STRING;
END;
GO

REMOVE_DUPLICATE_SPACES Version 2 – utilisation d’une boucle sur caractère

CREATE OR ALTER FUNCTION F_REMOVE_DUPLICATE_SPACES_V2(@STRING NVARCHAR(max))
RETURNS NVARCHAR(max)
AS
BEGIN
   DECLARE @STR_OUT NVARCHAR(MAX) = N'', @I INT = 1, @C NCHAR(1), @SPACE BIT = 0;
   WHILE @I <= LEN(@STRING)
   BEGIN
      SET @C = SUBSTRING(@STRING, @I, 1);
      -- caractère trouvé espace et caractère précédent non espace
	  IF @C = N' ' AND @SPACE = 0
      BEGIN
         SET @STR_OUT = @STR_OUT + ' ';
         SET @SPACE = 1;
      END
      ELSE
      BEGIN
         -- caractère non espace et caractère précédemment espace
         IF @C <> N' ' AND @SPACE = 1
         BEGIN
            SET @STR_OUT = @STR_OUT + @C;
            SET @SPACE = 0
         END
         ELSE
         BEGIN 
            -- caractère non espace et caractère précédemment non espace
            IF @C <> N' ' AND @SPACE = 0
               SET @STR_OUT = @STR_OUT + @C;
         END;
		 -- dernier cas : caractère espace et caractère précédent espace.... On ne fait rien !
      END;
      SET @I = @I + 1;
   END
   RETURN @STR_OUT
END;
GO

À nouveau un test simple nous permet de savoir si ces fonctions renvoient bien le résultat attendu :

-- test basique :
SELECT dbo.F_REMOVE_DUPLICATE_SPACES_V1('   toto  titi     tata        ');
SELECT dbo.F_REMOVE_DUPLICATE_SPACES_V2('   toto  titi     tata        ');
GO

Nous avons lancé ces fonctions sur la même table système que précédemment sys.sql_modules et dans la même base (plus d’un millier d’objets). En voici les résultats :

--> test sur bonne volumétrie
SET STATISTICS TIME ON;
GO
SELECT dbo.F_REMOVE_DUPLICATE_SPACES_V1(definition)
FROM   sys.sql_modules
--> Temps UC = 3 250 ms, temps écoulé = 3 607 ms

SELECT dbo.F_REMOVE_DUPLICATE_SPACES_V2(definition)
FROM   sys.sql_modules
--> Temps UC = 102 562 ms, temps écoulé = 116 270 ms.

CONCLUSION :

Autant que faire ce peut, évitez tout recours à une boucle et utilisez les fonctions intégrées fournies par MS SQL Server. Elles sont beaucoup plus rapides que le code interprété du Transact SQL…

Fonctions de traitement des chaines de caractères étude des performances – LE CODE !

Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *