SQL Server vu par Christian Robert

SQL Server : Pourquoi @toto IS NULL OR Champ = @toto pose problème ou l’intérêt du SQL dynamique

Prenons le cas d’une table ayant 5 champs :

CREATE TABLE [dbo].[MaTable](

      [Id] [int] IDENTITY(1,1) PRIMARY KEY,

      [Nom] [varchar](50) NOT NULL,

      [Param1] [int] NULL,

      [Param2] [int] NULL,

      [Param3] [int] NULL

)

GO

Je positionne un index sur chacun des champs ParamX :

CREATE INDEX Index1 ON MaTable(Param1)

CREATE INDEX Index2 ON MaTable(Param2)

CREATE INDEX Index3 ON MaTable(Param3)

J’ai une procédure stockée ayant 3 paramètre elle aussi : @P1, @P2 et @P3 dont la valeur par défaut est NULL et je souhaite pouvoir rechercher les enregistrements de ma table tel que Param1 = @P1, etc. si le paramètre correspondant est bien passé dans ma procédure stockée. Ce qui donne le code suivant, si l’un des paramètres est à NULL on a la condition complète qui évaluée à vrai grâce à la condition logique OR indiqué et on ignore ainsi le paramètre.

CREATE PROC [dbo].[MaProc] @P1 int = NULL, @P2 int = NULL, @P3 int = NULL

AS

SELECT *

  FROM [Database1].[dbo].[MaTable]

WHERE (@P1 IS NULL OR Param1 = @P1)

  AND (@P2 IS NULL OR Param2 = @P2)

  AND (@P3 IS NULL OR Param3 = @P3)

GO

Cette construction est assez logique, mais une surprise nous attend lors de l’exécution. Je charge cette table avec 500 000 enregistrements avec des valeurs uniques et j’exécute tout simplement :

-- Nombre de pages lues 871

EXEC dbo.MaProc 1

Résultat 871 pages lues (alors que la table fait 3394 pages), ce qui est énorme et représente la taille totale de Index1. Pour s’en convaincre jetez un coup d’œil au plan d’exécution qui est un Index Scan non ordonnée et complet sur l’index Index1, là où idéalement on devrait avoir une recherche sur Index1 ! Essayons maintenant sur @P2 :

-- Nombre de pages lues 1500871

EXEC dbo.MaProc @P2 = 1

Ici encore plus, ceci est du au comportement de la mise en cache du plan d’exécution et sa réutilisation. N’oubliez pas que c’est une procédure stockées et à ce titre le moteur reprend « bêtement » le précédent plan qui utilise le mauvais index :o(

Pourquoi ce comportement ? On pourrait croire que la requête est formulée correctement, eh bien non cet IS NULL OR est à proscrire du code SQL car engendre des dégradations très importantes. Une autre formulation à bannir est ChampA IS NOT NULL AND ChampA = X qui pose des problèmes similaires en termes de performances.

Quelles sont les alternatives ?

Branchement multiples :

ALTER PROC [dbo].[MaProc] @P1 int = NULL, @P2 int = NULL, @P3 int = NULL

AS

 

IF @P1 IS NOT NULL

      SELECT *

        FROM [Database1].[dbo].[MaTable]

      WHERE Param1 = @P1

 

IF @P2 IS NOT NULL

      SELECT *

        FROM [Database1].[dbo].[MaTable]

      WHERE Param2 = @P2

       

IF @P3 IS NOT NULL

      SELECT *

        FROM [Database1].[dbo].[MaTable]

      WHERE Param3 = @P3

 

GO

Cette requête n’est pas un bon remplaçant de ma précédente, mais c’est pur l’exemple et limiter le nombre de lignes de codes. Je teste la présence du paramètre dans les blocs IF, si il est passé je l’utilise dans la bonne requête.

-- Nombre de pages lues 3

EXEC dbo.MaProc 1

-- Nombre de pages lues 3

EXEC dbo.MaProc @P2 = 1

Et là le plan est correct dans les 2 cas… On a bien une recherche sur Index1 dans le premier et sur Index2 dans le second, soit une requête 500 000 fois plus performante que dans le pire des cas précédent.

SQL Dynamique :

ALTER PROC [dbo].[MaProc] @P1 int = NULL, @P2 int = NULL, @P3 int = NULL

AS

 

      DECLARE @param nvarchar(500)

      DECLARE @sql nvarchar(max)

 

      set @param = N''

      set @sql = N'SELECT * FROM [Database1].[dbo].[MaTable] WHERE 1 = 1 '

 

      IF @P1 IS NOT NULL

            SET @param = @param + N' AND Param1 = ' + CAST(@P1 as nvarchar(12))

 

      IF @P2 IS NOT NULL

            SET @param = @param + N' AND Param2 = ' + CAST(@P2 as nvarchar(12))

 

      IF @P3 IS NOT NULL

            SET @param = @param + N' AND Param3 = ' + CAST(@P3 as nvarchar(12))

 

      SET @sql = @sql + @param

     

      EXEC(@sql)

 

GO

Cette fois ci la requête remplace à 100% celle de départ, on concatène une chaîne qui est la requête à exécuter avec son paramètre. Regardons maintenant les performances :

-- Nombre de pages lues 3

EXEC dbo.MaProc 1, 2, 3

Dans tous les cas, que vous passiez 1 ou plusieurs paramètres le nombre pages lues est toujours de 3 et le plan utilise la recherche sur les bons index. On a ici le résultat optimal… Bon ok je peux faire encore un peu mieux avec un sp_executesql au lieu du EXEC(), mais pour rester concis cette requête là ira bien.

L’intérêt du SQL dynamique est ici énorme il vous évite la création d’une procédure qui aurait due tester toutes les combinaisons de passage de paramètre, qui croissent en fonction du nombre de ce dernier. On n’aura donc ici pas d’autres choix que de générer la requête qui va bien !

Bonnes requêtes…