Ces derniers jours chez un client, j’ai dû faire face à une soudaine augmentation de l’activité CPU qui était en moyenne à 85% avec des pointes à 100% avec tous les problèmes de ralentissement d’activité que cela implique. Après une première analyse, la configuration hardware et la configuration du serveur SQL n’étaient pas en cause. J’ai donc décidé de lancer une trace avec SQL Profiler pour détecter les requêtes consommatrices en ressources CPU.

Voici le résultat de la trace :

On constate qu’une procédure stockée est particulièrement consommatrice en ressources CPU et en nombre de lectures de pages.

Voici maintenant le code exécutée par cette procédure :

SELECT code_acte

FROM table1      
WHERE numero_version =@numeroVersion

                   WHERE numero_version = @numeroVersion 
                    AND code_activite <> @codeActe)     
  AND dbo.actevalide(code_acte,@numeroVersion, @dateEffet) = 1     
GROUP BY code_acte     
HAVING COUNT(code_acte) = 1

On remarque que dans la restriction il y a un appel à une fonction scalaire (dbo.actevalide)
Voici le code de cette fonction :

SELECT  TOP 1  
@dateFinActe = DATE_FIN, @dateDebutActe = DATE_CREATION    
FROM  table2    
WHERE  CODE_ACTE = @codeActe   
AND NUMERO_VERSION = @numeroVersion  

If (@dateFinActe = 0 Or @dateFinActe >= @dateEffet) And @dateDebutActe <= @dateEffet   
Set @acteValide = 1   
Else   
Set @acteValide = 0   
– Renvoi de la valeur   
Return @acteValide  

Cette fonction récupère pour un code d’activité et un numéro de version la date de création et de fin de validité d’un acte. Elle vérifie ensuite, par rapport à ces dates, si la date d’effet de l’acte est valide.

On constate que pour chaque ligne de la requête principale, un appel à la fonction scalaire est fait. Cette fonction va donc lire autant de fois la table2 pour récupérer les dates de l’acte qu’il y aura de ligne retournée par la requête principale. Un traitement ligne à ligne est donc fait par le moteur SQL. Dans la plupart des cas il est possible de remplacer un tel traitement par un autre traitement ensembliste proposant le même résultat :

SELECT n.code_acte

FROM dbo.table1 n 
INNER JOIN (SELECT CODE_ACTE,

                       AND n2.code_activite <> @codeActe)  
AND n.numero_version = @numeroVersion  
GROUP BY n.code_acte    
HAVING COUNT(n.code_acte) = 1

Voyons maintenant ce que cela donne en terme de performance :

         ———————————-
         |       Lectures    |     CPU (ms)   
—————————————-
Avant |      26293         |     9547         
—————————————-
Apres |        739           |       78           
—————————————-

Les résultats parlent d’eux même !!

Que devons nous retenir ? L’utilisation des UDF sous SQL Server est une fonctionnalité intéressante mais elle est à utiliser avec parcimonie. Bien souvent les programmeurs optent pour la facilité et par habitude vont tendre vers un traitement procédural ou itératif, ce qui est une énorme erreur en programmation SQL. Il faut toujours garder à l’esprit que le langage SQL est un langage ensembliste et que tout autre forme de traitement sera toujours moins performant !!!

Bonne programmation !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon