Requêtes avec paramètres optionels + TMC

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

Je ne suis pas expert...

Mais je te préconniserais la seconde aussi effectivement.

En effet, si ta première query effectue un table scan... C'est pas top... Enfin, ca depend du nombre de rows de ta table... Si t'en à 10 ca va, si t'en as des millions... Les index sont la et il vaut mieux s'en servir, ca accélère pas mal les choses ;)

La seconde c'est du SQL dynamique, c'est ce qu'on utilise "partout" ici, ca va très vite à construire et tu as une requète propre.
Si tu veux faire du bebugging, histoire de savoir ce qui est executer, tu peux toujours remplacer ton execute par un print...
En plus si tu fais une analyse de la quantité de parametre passée en moyenne, ca te permettrais de pouvoir optimiser tes index en fonction de ca aussi... (Fait quand même gaffe à ne pas bousiller des index qui aurait un impact sur d'autres applications dont tu n'as peut être pas connaissance)

Bonjour,

Je suis d'accord avec Ptit_Dje.

La 1e procédure stockée n'est pas optimisable par le simple fait qu'elle contient des OR (ce n'est pas un SARG). De mon expérience, un index n'est jamais pris en compte par l'optimiseur de requêtes lorsque les restrictions contiennent des OR. Donc l'optimiseur de requête va choisir le scqn de table et là c'est lent :D

La seconde méthode te permettra effectivement de construire ta requête proprement, et si tu connais à peu près les paramètres qu'on te passe le plus souvent, tu peux facilement indexer la table, et là c'est rapide :D

Bonjour,

Et dans le cas où mes critères ne sont pas du genre :
MaTable.Champ2 = @Param2
Mais :
MaTable.Champ2 Like '%' + @Param2 + '%'
?

Le like étant vraiment "gourmand" je te recommenderai de l'éviter si possible.

Il est d'autant + important de limiter tes where clauses au minimum car si tu execute des like inutiles, ca risque de te prendre d'autant plus de temps...

Je sais que le LIKE est très gourmand et qu'en plus il ne permet pas l'utilisation des index. Cependant je n'ai vraiment pas le choix, cela fait parti des besoins fonctionnels de l'écran web appelant cette procédure stockée.

Cependant, la clause devient
AND (@Param2 IS NULL OR MaTable.Champ2 LIKE '%' + @Param2 + '%')

Dans le cas où @Param2 est Null on ne fait rien... (?)

Honettement je reste persuadé que le gain de perf entre la première solution et la seconde est très minime (de l'ordre de quelque pourcents) et qu'il ne mérité par qu'on sacrifie la maintenabilité du projet (un des avantages des procédures stockées est justement qu'à leur "compilation" on vérifie que si les champs existe toujours... alors qu'avec la seconde solution le seul moyen de voir que la proc ne marche plus est de se mettre dans un cas d'utilisation).