SQL Server : Pourquoi @toto IS NULL OR Champ = @toto pose problème ou lintérêt du SQL dynamique
Prenons le cas dune 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)
Jai 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 lun 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 lexécution. Je charge cette table avec 500 000 enregistrements avec des valeurs uniques et jexé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 sen convaincre jetez un coup dil au plan dexécution qui est un Index Scan non ordonnée et complet sur lindex 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 dexécution et sa réutilisation. Noubliez pas que cest 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 nest pas un bon remplaçant de ma précédente, mais cest pur lexemple 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 lutilise 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.
Lintérêt du SQL dynamique est ici énorme il vous évite la création dune procédure qui aurait due tester toutes les combinaisons de passage de paramètre, qui croissent en fonction du nombre de ce dernier. On naura donc ici pas dautres choix que de générer la requête qui va bien !
Bonnes requêtes
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 :