Bonjour,
Je souhaiterais avoir des avis "d'expert" concernant un débat entre différentes manières d'écrire des procédures stockées avec utilisation de critères optionnels.
Pour ce qui me concerne je préfère la première méthode qui est de loin la meilleur au niveau de la maintenance. Cependant la seconde est, semble-t-il plus performante pour plusieurs raisons :
- la première méthode réutilise le même plan d'exécution entre les différents appels de la proc, ce qui est un désavantage étant donnée que les paramètre d'entrer sont aléatoires. Je crois qu'il est possible de forcer le recalcul du plan d'exécution d'une proc mais je ne sais pas comment... et de plus je ne sais pas si c'est pour autant que le résultat sera meilleur.
- il semblerait que la première méthode parcours le table scan sur l'ensemble de la table --> problème éventuels de lock, etc...
Malheureusement, les personnes s'occupant des TMC préconisent la seconde méthode, qui est celon eux, et de loin, nettement plus performante.
1/ Première méthode
CREATE PROC dbo.Test (
@Param1 int,
@Param2 int = Null, --Paramètre optionel
@Param3 int = Null, --Paramètre optionel
@Param4 int = Null, --Paramètre optionel
@Param5 int = Null) --Paramètre optionel
AS
BEGIN
SELECT Champ1, Champ2, Champ3
FROM MaTable
WHERE
MaTable.Champ1 = @Param1
AND (@Param2 IS NULL OR MaTable.Champ2 = @Param2)
AND (@Param3 IS NULL OR MaTable.Champ3 = @Param3)
AND (@Param4 IS NULL OR MaTable.Champ4 = @Param4)
AND (@Param5 IS NULL OR MaTable.Champ5 = @Param5)
END
2/ Seconde méthode
CREATE PROC dbo.Test (
@Param1 int,
@Param2 int = Null, --Paramètre optionel
@Param3 int = Null, --Paramètre optionel
@Param4 int = Null, --Paramètre optionel
@Param5 int = Null) --Paramètre optionel
AS
BEGIN
DECLARE @strRequete VarChar(8000)
SET @strRequete = 'SELECT Champ1, Champ2, Champ3
FROM MaTable
WHERE MaTable.Champ1 = @Param1'
IF @Param2 IS NOT NULL
BEGIN
SET @strRequete = @strRequete + ' AND MaTable.Champ2 = @Param2'
END
IF @Param3 IS NOT NULL
BEGIN
SET @strRequete = @strRequete + ' AND MaTable.Champ3 = @Param3'
END
IF @Param4 IS NOT NULL
BEGIN
SET @strRequete = @strRequete + ' AND MaTable.Champ4 = @Param4'
END
IF @Param5 IS NOT NULL
BEGIN
SET @strRequete = @strRequete + ' AND MaTable.Champ5 = @Param5'
END
-- Exécution de la requête
exec sp_executesql @strRequete
END
Partager