[optimisation] arguments procédure stocké

Version imprimable

10/05/2010, 16h11
freuh94

[optimisation] arguments procédure stocké

Bonjour,

Je me retrouve à faire des procédures stokées pas plus compliquées que ça mais qui prennent des paramètres en arguments..

Or si j'éxécute ma requête de façon "en dure" elle met 5 secondes mais si je remplace les valeurs statics par des arguments elle met plus de 45minutes? 8O8O8O8O8O

Pourquoi ô monde si cruel, tu met temps de temps!!!! :?:?

Je croyais que les procédures stockées étaient justement fais pour ça!!

je fais comment moi, pour filtrer mes résultats de mes tables si il accroit de façon exponentiel le temps alors que je lui rajoute des arguments...

:cry:

Merci pour votre aide :ccool:
10/05/2010, 16h13
kagemaru

bonjour

il faudrait qu'on en sache un peu plus (code sql, plan d'éxécution, statistics io)

merci

ah oui dsl ,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT		s.reference AS 'Référence', s.prom AS 'Prom', s.nom AS 'Nom', DATEDIFF(YEAR, se.d_naissance, GETDATE()) AS 'Age', COUNT(*)
FROM		mc JOIN s ON s.reference =  mc.reference
			JOIN se ON se.reference = s.reference
WHERE		mc.codification like 'TEA%'
			AND	s.reference IN ( SELECT reference FROM table3 where d_sys between '01/01/2010' and GETDATE())
			AND se.c1 like '%'
			AND se.c2 like '%'
			AND se.c3 like '%'
			AND se.c8 like '%'
			AND s.ville like '%'
GROUP BY	s.reference, s.prom, s.nom, se.d_naissance
HAVING		COUNT(*) >= 4
ORDER BY	s.reference ASC

puis la procédure stoké :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
DECLARE arg ...
 
AS 
 
SELECT		s.reference AS 'Référence', s.prom AS 'Prom', s.nom AS 'Nom', DATEDIFF(YEAR, se.d_naissance, GETDATE()) AS 'Age', COUNT(*)
FROM		mc JOIN s ON s.reference =  mc.reference
			JOIN se ON se.reference = s.reference
WHERE		mc.codification like @arg1 
			AND	s.reference IN ( SELECT reference FROM table3 where d_sys between @arg2 and @arg3)
			AND se.c1 like @arg4 
			AND se.c2 like @arg5 
			AND se.c3 like @arg6 
			AND se.c8 like @arg7 
			AND s.ville like @arg8 
GROUP BY	s.reference, s.prom, s.nom, se.d_naissance
HAVING		COUNT(*) >= @arg9
ORDER BY	s.reference ASC

Voilà :mrgreen:

Bonjour,

Je ne comprend pas l'utilisation que vous faites de LIKE.
En effet, LIKE '%' ne vous permet pas de rechercher le caractère symbolisant un pourcentage, mais une chaîne de caractères contenant n'importe quel caractère, y compris une chaîne vide.
Sachez ensuite que tout prédicat LIKE utilisant le joker % autre part qu'en fin de prédicat n'est pas SARGable.
Si vous souhaitez chercher le caractère symbolisant le pourcentage, jetez un œil ici ;)
C'est de l'utilisation que vous faites de LIKE

Autre petite remarque : qualifiez le nom des objets qui participent à vos requêtes avec le nom du schéma auquel ils appartiennent.
Cela évite au moteur de base de données de le rechercher dans les tables de métadonnées ;)
Enfin une colonne n'est pas une valeur, donc il est incorrect de la "coder" comme une valeur de chaîne.

Donc votre requête non paramétrée pourrait s'écrire comme suit :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 SELECT s.reference AS Référence , s.prom AS Prom , s.nom AS Nom , DATEDIFF(YEAR, se.d_naissance, GETDATE()) AS Age , COUNT(*) FROM dbo.mc JOIN dbo.s ON s.reference = mc.reference JOIN dbo.se ON se.reference = s.reference JOIN dbo.table3 AS t ON s.reference = t.reference AND t.d_sys BETWEEN '20100101' AND GETDATE() WHERE mc.codification LIKE 'TEA%' AND se.c1 LIKE '\%' AND se.c2 LIKE '\%' AND se.c3 LIKE '\%' AND se.c8 LIKE '\%' AND s.ville LIKE '%' GROUP BY s.reference, s.prom, s.nom, se.d_naissance HAVING COUNT(*) >= 4 ORDER BY s.reference ASC
quels sont les index sur les tables qui participent à la requête ?
Si vous souhaitez paramétrer celle-ci, il vous faudra tester dans votre procédure stockée que la variable ne contient pas de % ou de crochets, auquel cas il vous faudra créer votre prédicat à l'aide d'ESCAPE.

@++ ;)

Bonjour,

Tout d'abord merci pour votre aide construite et bien expliquée.

Premier point, je cherche non pas un pourcentage mais belle et bien '%' (joker qui prend tout).
En effet, ma requête sert à filtrer certaines colonnes avec des paramètres. Donc si le paramètre est vide, il faut tout prendre. D'où le '%'.
Je ne sais pas si je m'exprime bien , désolé.

Deuxième point, j'ai bien entendu commencé par faire une jointure pour la table3 mais cela était vraiment plus long. Pourquoi? parce que j'en sais rien.
En plus je ne suis pas fan de la recherche dans le IN (...) avec une autre requête mais c'est plus rapide alors...

Exemple typique de l'utilisation du la procédure stokée de nom TEST:
si je veux filtrer par ville et en suposant quelle prenne en compte 7 arguments (date début période, date fin période, c1, c2, c3, c4, ville):

Code:

TEST '', '', '', '', '', '', 'PARIS'

Valeur default dans la procédure stokée:
date début : '010120010'
date fin : getDate()
c1 : '%'
c2 : '%'
c3 : '%'
c8 : '%'

qui donne :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 SELECT s.reference AS Référence , s.prom AS Prom , s.nom AS Nom , DATEDIFF(YEAR, se.d_naissance, GETDATE()) AS Age , COUNT(*) FROM dbo.mc JOIN dbo.s ON s.reference = mc.reference JOIN dbo.se ON se.reference = s.reference JOIN dbo.table3 AS t ON s.reference = t.reference AND t.d_sys BETWEEN '20100101' AND GETDATE() WHERE mc.codification LIKE 'TEA%' AND se.c1 LIKE '%' AND se.c2 LIKE '%' AND se.c3 LIKE '%' AND se.c8 LIKE '%' AND s.ville LIKE 'PARIS%' --il peut y avoir PARIS-NORD PARIS-SUD GROUP BY s.reference, s.prom, s.nom, se.d_naissance HAVING COUNT(*) >= 4 ORDER BY s.reference ASC
mais c'est toujours aussi long avec les arguments (en plus de la jointure)

Merci encore , bonne journée :ccool:

Bonjour,

Voila comment moi j'aurais écrit votre procédure :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT		s.reference AS 'Référence', s.prom AS 'Prom', s.nom AS 'Nom', DATEDIFF(YEAR, se.d_naissance, GETDATE()) AS 'Age', COUNT(*)
FROM		mc
INNER JOIN s ON s.reference =  mc.reference
INNER JOIN se ON se.reference = s.reference
WHERE			(@arg1 = '' OR mc.codification LIKE @arg1)
			AND	EXISTS (SELECT * FROM reference FROM table3 WHERE table3.reference = s.reference AND d_sys BETWEEN @arg2 AND @arg3)
			AND (@arg4 = '' OR se.c1 LIKE @arg4 )
			AND (@arg5 = '' OR se.c2 LIKE @arg5 )
			AND (@arg6 = '' OR se.c3 LIKE @arg6 )
			AND (@arg7 = '' OR se.c8 LIKE @arg7 )
			AND (@arg8 = '' OR s.ville LIKE @arg8 )
GROUP BY	s.reference, s.prom, s.nom, se.d_naissance
HAVING		COUNT(*) >= @arg9
ORDER BY	s.reference ASC

A tester maintenant..

11/05/2010, 15h55
freuh94

Je ne comprend pas pourquoi mais c'est plus rapide...

Merci beaucoup!

Si ils y en a qui s'ennui... j'ai lu , grâce au lien juste au dessut, qu'il fallait évité les 'OR'... Or là, David.V en rajoute !! et c'est plus rapide.

Bref merci encore
:ccool:
11/05/2010, 16h23
kagemaru

Citation:

Je ne comprend pas pourquoi mais c'est plus rapide...

Il faudrait comparer les plans d'exécution et la consommation en E/S. Vous y trouverez sûrement la réponse.
12/05/2010, 02h32
elsuket

Citation:

Envoyé par freuh94

j'ai lu , grâce au lien juste au dessut, qu'il fallait évité les 'OR'... Or là, David.V en rajoute !! et c'est plus rapide.

Un OR entre une variable et une colonne fonctionne très bien.
En revanche entre 2 colonnes ... ;)

@++ ;)