Discussion :

[FredLo]

Bonjour à tous!

Je suis en train de développer une interface avec une base de données qui affiche des résultats en fonction de filtres choisis par l'utilisateur, qu'il peut bien sûr superposer. J'aimerais écrire des procédures stockées pour ces requêtes, mais je m'aperçois que selon les critères choisis ou non, je dois en écrire énormément afin d'avoir toutes les combinaisons (j'ai 7 critères différents).

Pourtant, cela doit être assez fréquent, ai-je manqué quelques chose? Pourrais-je écrire une procédure stockée où les paramètres d'entrée peuvent être ignorés? Je travaille sous SQL Server 2005 pour information.

voici un exemple de ma procédure:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
CREATE PROCEDURE [dbo].[P_ProducerSearch]  
	-- Add the parameters for the stored procedure here
	@Variety	varchar(100) = '*',
	@Region		varchar(100) = '*',
	@Country	varchar(100) = '*',
	@Color		int = 0
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
SELECT T_WineType.ProducerName 
FROM T_WineType INNER JOIN T_Region ON T_WineType.RegionID = T_Region.RegionID 
		INNER JOIN T_Country ON T_Region.CountryID = T_Country.CountryID 
		INNER JOIN T_Wine ON T_WineType.WineTypeID = T_Wine.WineTypeID 
		INNER JOIN T_Bottle ON T_Bottle.WineID = T_Wine.WineID 
		LEFT JOIN T_Cepage ON T_Cepage.CepageID = T_WineType.CepageID
 
WHERE T_WineType.VarietyName LIKE @Variety
	AND T_Region.RegionName LIKE @Region
	AND T_Country.CountryName LIKE @Country
	AND T_WineType.Color = @Color
 
END

Dans ce cas, j'ai mis '*' comme valeur par défaut, mais ça ne marche pas. il faudrait une valeur par défaut qui soit ignorée par la requête en fait (même chose pour la valeur entière).


Merci d'avance pour votre aide, et bonnes fêtes de fin d'année!

Frédéric.

[SQLpro]

Il serait plus simple de faire cela en SQL dynamique.

A +

[FredLo]

Merci de ta réponse, actuellement c'est de cette façon que je procède, je pensais pouvoir le faire avec une procédure stockée...

[elsuket]

Bonjour,

Je pense que SQLPro entendait par SQL dynamique le fait de construire la chaîne de requête dans une procédure stockée.
Vous pouvez en plus de cela utiliser la procédure stockée système sp_executeSQL qui vous procurera un peu plus de puissance et une meilleure réutilisation de votre cache de plans, en testant si vos paramètres d'entrée sont NULL ou pas, par exemple ...

@++

[FredLo]

Salut Elsuket,

Merci de cette précision, j'avais mal compris. Pour le moment, je construis ma requête dans mon code c++, selon les choix de l'utilisateur, puis je l'execute grâce à un objet ADO.

Finalement, ce que tu me proposes, c'est de faire la même chose dans une procédure stockée, donc en utilisant des IF...ELSE afin de tester tous les paramêtres d'entrée? Mais puisque la requête est différente selon les cas, comment le plan d'execution peut-il être précompilé?

Concernant sp_executeSQL (merci de l'info!), tu me conseilles donc de l'utiliser à l'intérieur de ma procédure stockée?


Encore une fois, merci de ton aide,

Frédéric.

EDIT: en cherchant un peu du côté des requêtes dynamiques, je trouve plein de posts intéressants, je risque donc de tomber sur la solution...

[elsuket]

Mais puisque la requête est différente selon les cas, comment le plan d'execution peut-il être précompilé?

Les plans de requête sont pré-compilés aux valeurs des paramètres près.
D'ailleurs si vous faites une session de SQL Profiler, vous verrez passer des paramètres dans les RPC avec @p1 = uneValeur, ... : c'est de cela qu'il s'agit

Finalement, ce que tu me proposes, c'est de faire la même chose dans une procédure stockée, donc en utilisant des IF...ELSE afin de tester tous les paramêtres d'entrée?

Oui, à l'avantage de T-SQL que si vous avez besoin de changer le code sans changer les paramètres d'entrée, il vous suffit de recompiler la procédures stockée

Concernant sp_executeSQL (merci de l'info!), tu me conseilles donc de l'utiliser à l'intérieur de ma procédure stockée?

Tout à fait

EDIT: en cherchant un peu du côté des requêtes dynamiques, je trouve plein de posts intéressants, je risque donc de tomber sur la solution...

Vous pouvez effectivement vous en servir comme source d'inspiration, mais dans votre cas vous devrez "valuer" par défaut vous paramètres d'entrée à NULL, et réaliser vos appels en C++ en nommant les paramètres

@++

[FredLo]

Merci de tes précisions, je crois que j'ai compris comment faire, par contre, cela m'amène à plusieurs interrogations d'ordre général:

- Si l'on peut générer n'importe quelle requête dans une procédure stockée, il n'y a aucun intérêt à le générer ailleurs (dans le code de l'IU par exemple)?

- Je pensais que les procédures stockées gardaient en mémoire toujours le même plan d'exécution, c'est cela qui leur permettait d'être plus rapide. Mais d'après ce que tu me dis, le plan varie en fonction des paramètres d'entrée, où est-donc le gain de vitesse alors?


Frédéric.

[elsuket]

Si l'on peut générer n'importe quelle requête dans une procédure stockée, il n'y a aucun intérêt à le générer ailleurs (dans le code de l'IU par exemple)?

Les frameworks comme les SGBDR sont majoritairement mal utilisés.
J'ai également écrit un petit billet autour de votre question

Mais d'après ce que tu me dis, le plan varie en fonction des paramètres d'entrée, où est-donc le gain de vitesse alors?

Attention, j'ai écrit :

Les plans de requête sont pré-compilés aux valeurs des paramètres près.

Ce qui veut dire que pour la même chaîne de caractères, si seules les valeurs des paramètres changent, alors le même plan de requête est réutilisé.
C'est ce qui explique les @p1 que vous pouvez voir, comme je vous l'ai dit, dans l'interface graphique de SQL Profiler.
Néanmoins, si cela procure la plupart du temps de bonnes performances, c'est aussi cela qui est à l'origine des problèmes de parameter sniffing

@++

[FredLo]

Super, merci Elsuket pour ces précisions!