[Procedure Stockée] Optimisation

Version imprimable

On me crie dessus parce que ma Procédure stockée est un peu longue à s'éxécuter :aie: :aie: :aie:

Voilà une bestiole qui met 25 secondes à s'éxécuter pour afficher 4035 lignes. Question : Est-ce normal ? Quels peuvent être les éléments limitants ? Les types (varchar, text etc...) pourrait-ils être à l'origine de la lenteur d'éxécution ?

D'avance, merci pour vos éclaircicements :)

Code:

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
28
29
30
31
32
33
34
SELECT	id_demande_demande_au_plan.id as id ,
id_demande_demande_au_plan.id_demande_reel as id_demande_reel,
id_demande_demande_au_plan.id_demande_au_plan as id_demande_au_plan,
formation_plan = CASE DemandesAuPlan.guide WHEN 1 THEN (SELECT titre FROM Formations WHERE DemandesAuPlan.id_formation = Formations.id_formation)	WHEN 0 THEN (SELECT titre FROM Formations_Demandes WHERE DemandesAuPlan.id_formation = Formations_Demandes.id_formation) ELSE ' ' END,
ISNULL(Plans_plan.nom,Plans.nom) as nom_plan,
libelle_etat = CASE ISNULL(id_demande_demande_au_plan.id_demande_reel,-1) WHEN  -1 THEN 'Au Plan' ELSE (SELECT libelle_etat from etat_demande where demandes.etat = Etat_demande.id_etat) END,
etat = CASE ISNULL(convert(varchar,id_demande_demande_au_plan.id_demande_reel),-1) WHEN -1 THEN '9' ELSE CONVERT(varchar(50),demandes.etat) END,
nom_user_plan = ISNULL((SELECT nom FROM Utilisateurs WHERE DemandesAuPlan.id_utilisateur = Utilisateurs.id_utilisateur),' '),
prenom_plan = ISNULL((SELECT prenom FROM Utilisateurs WHERE DemandesAuPlan.id_utilisateur = Utilisateurs.id_utilisateur),' '),
DemandesAuPlan.id_utilisateur as id_utilisateur_au_plan,				nbre_heures_plan = CAST(DemandesAuPlan.nbre_heures as Decimal(18,2)),
cout_pedagogique_plan = CAST(DemandesAuPlan.cout_pedagogique as Decimal(18,2)),				
titre = CASE Demandes.guide WHEN 1 THEN Formations.titre WHEN 0 THEN Formations_Demandes.titre ELSE ' ' END,
nbre_heures = CAST(Demandes.nbre_heures as numeric(18,2)),
cout_pedagogique = CAST(Demandes.cout_pedagogique as numeric(18,2)),		Demandes.id_utilisateur,
nom = ISNULL((SELECT nom FROM utilisateurs WHERE Demandes.id_utilisateur = Utilisateurs.id_utilisateur),' '),
prenom = ISNULL((SELECT prenom FROM utilisateurs WHERE Demandes.id_utilisateur = Utilisateurs.id_utilisateur),' ')
 
FROM id_demande_demande_au_plan 
LEFT JOIN Demandes ON id_demande_demande_au_plan.id_demande_reel = Demandes.id_demande 
LEFT JOIN DemandesAuPlan ON id_demande_demande_au_plan.id_demande_au_plan = DemandesAuPlan.id_demande 
LEFT JOIN Plans ON Demandes.id_plan = Plans.id_plan
LEFT JOIN Utilisateurs ON Demandes.id_utilisateur = Utilisateurs.id_utilisateur 
LEFT JOIN Utilisateurs AS Utilisateurs_Responsable ON Demandes.id_responsable = Utilisateurs_Responsable.id_utilisateur
LEFT JOIN Formations ON Demandes.id_formation = Formations.id_formation 
LEFT JOIN Formations_Demandes ON Demandes.id_formation = Formations_Demandes.id_formation 			
LEFT JOIN Plans as Plans_Plan ON DemandesAuPlan.id_plan = Plans_plan.id_plan 
LEFT JOIN Formations as Formations_Plan ON DemandesAuPlan.id_formation = Formations_plan.id_formation 
LEFT JOIN Utilisateurs as Utilisateurs_Plan ON DemandesAuPlan.id_utilisateur = Utilisateurs_plan.id_utilisateur 
LEFT JOIN Utilisateurs AS Utilisateurs_Responsable_plan ON  DemandesAuPlan.id_responsable = Utilisateurs_Responsable_plan.id_utilisateur
LEFT JOIN Formations_Demandes as Formations_Demandes_Plan ON DemandesAuPlan.id_formation = Formations_Demandes_plan.id_formation 
 
WHERE (Plans.Annee = @annee OR Plans_plan.Annee = @annee) 
 
ORDER BY id,nom_user_plan,prenom_plan,nom,prenom

27/06/2007, 11h55
rudib
Bonjour,

Peux-tu faire un
Code:

1 2 SET SHOWPLAN_ALL ON GO
Avant d'exécuter ce SELECT, et nous envoyer le résultat ?

Sinon, quelques infos sur les outils d'optimisation : http://rudi.developpez.com/sqlserver.../optimisation/
27/06/2007, 12h59
lapanne

merci pour le lien :)

Voilà une capture du résultat du showplan_all. ça a l'air de bien poutrer, y'a plein de chiffres toussa, je vais me pencher dessus :aie:

http://www.iwanu-ga-hana.fr//htdocs/Rssources/show_all.gif

Le fichier excel tout moche
27/06/2007, 13h53
serge0934

ce sont tes appels a la fonction isnull dans les cas

Code:

prenom_plan = ISNULL((SELECT prenom FROM Utilisateurs WHERE DemandesAuPlan.id_utilisateur = Utilisateurs.id_utilisateur),' '),

il vaut mieux que tu fasses un autre left outer join sur utilisateurs
27/06/2007, 14h01
lapanne

Tu t'appuies sur le rapport showplan_all pour affirmer ça ? Parce que je pige pas tout au rapport, je me documente :aie: :aie:

Sinon, j'ai virer tout les ISNULL, je tombe toujours entre 25 et 28 secondes de traitements :roll:

Edit : en mettant un simple count(*) à la place de mes colonnes, je tombe à 24 secondes ce qui n'est pas vraiment flagrant comme delta :cry:
27/06/2007, 14h09
David.V

4000 lignes c'est pas énorme, mais il faut voir les volumes de données de tes différentes tables.

Déjà si tu pouvais t'arranger pour ne plus avoir les sous requetes dans les case et les isnull, tu devrais gagner beaucoup de temps. Passe par des jointures externes gauche comme dit plus haut.
27/06/2007, 14h21
rudib

Les remarques de mes confrères sont valides. C'est une idée intéressante d'avoir fait une copie d'écran du plan d'exécution, à part le fait que c'est inutilisable parce qu'on ne voit rien. Ne peux-tu pas le poster en résultat texte ?

y'a un lien vers un fichier excel 8-) :roll:

ze le remet : http://www.iwanu-ga-hana.fr/Rssources/showplan_all.xls

Edit : en faisant ceci, je tombe à 22 secondes, ce qui n'est pas trés folichon non plus :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
SELECT	id_demande_demande_au_plan.id as id ,
id_demande_demande_au_plan.id_demande_reel as id_demande_reel,			id_demande_demande_au_plan.id_demande_au_plan as id_demande_au_plan,														
Demandes.id_utilisateur,
nom = Utilisateurs.nom,
prenom = Utilisateurs.prenom
 
FROM id_demande_demande_au_plan 
LEFT JOIN Demandes ON id_demande_demande_au_plan.id_demande_reel = Demandes.id_demande 
LEFT JOIN DemandesAuPlan ON id_demande_demande_au_plan.id_demande_au_plan = DemandesAuPlan.id_demande 
LEFT JOIN Plans ON Demandes.id_plan = Plans.id_plan
LEFT JOIN Utilisateurs ON Demandes.id_utilisateur = Utilisateurs.id_utilisateur 
LEFT JOIN Utilisateurs AS Utilisateurs_Responsable ON Demandes.id_responsable = Utilisateurs_Responsable.id_utilisateur
LEFT JOIN Formations ON Demandes.id_formation = Formations.id_formation 
LEFT JOIN Formations_Demandes ON Demandes.id_formation = Formations_Demandes.id_formation 			
LEFT JOIN Plans as Plans_Plan ON DemandesAuPlan.id_plan = Plans_plan.id_plan 
LEFT JOIN Formations as Formations_Plan ON DemandesAuPlan.id_formation = Formations_plan.id_formation 
LEFT JOIN Utilisateurs as Utilisateurs_Plan ON DemandesAuPlan.id_utilisateur = Utilisateurs_plan.id_utilisateur 
LEFT JOIN Utilisateurs AS Utilisateurs_Responsable_plan ON  DemandesAuPlan.id_responsable = Utilisateurs_Responsable_plan.id_utilisateur
LEFT JOIN Formations_Demandes as Formations_Demandes_Plan ON DemandesAuPlan.id_formation = Formations_Demandes_plan.id_formation 
 
WHERE (Plans.Annee = @annee OR Plans_plan.Annee = @annee) 
 
ORDER BY id,nom_user_plan,prenom_plan,nom,prenom

Sans la clause where je tombe à 1 secondes. Quelqu'un à des explications ?:aie:

Et pis la machine, c'est un P3 1,2 Ghz avec 768 mo de ram. C'est peu ?

27/06/2007, 14h50
rudib

Ok,

As-tu des index sur
id_demande_demande_au_plan.id_demande_reel
id_demande_demande_au_plan.id_demande_au_plan
DemandesAuPlan.id_plan
Plans.id_plan
??
27/06/2007, 15h14
lapanne

Merci beaucoup, j'ai indexé les tables où je faisais les jointures les plus lourdes (demandes, demande_au_plan, id_demande_demande_au_plan...) et je suis tombé à 4 secondes.

J'ai en plus virer les sous requetes qui ne servait strictement à rien vu que je faisais déjà la jointure(notamment sur Utilisateurs).

Merci beaucoup à tous pour votre aide. Si vous avez d'autres remarques n'hésitez pas.

Essayez celle là et dites m'en des nouvelles...

Code:

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SELECT	id_demande_demande_au_plan.id AS id ,
id_demande_demande_au_plan.id_demande_reel AS id_demande_reel,			
id_demande_demande_au_plan.id_demande_au_plan AS id_demande_au_plan,														
Demandes.id_utilisateur,
Utilisateurs.nom,
Utilisateurs.prenom
 
FROM id_demande_demande_au_plan 
LEFT JOIN Demandes ON id_demande_demande_au_plan.id_demande_reel = Demandes.id_demande 
LEFT JOIN DemandesAuPlan ON id_demande_demande_au_plan.id_demande_au_plan = DemandesAuPlan.id_demande 
LEFT JOIN Plans ON Demandes.id_plan = Plans.id_plan
LEFT JOIN Utilisateurs ON Demandes.id_utilisateur = Utilisateurs.id_utilisateur 
LEFT JOIN Utilisateurs AS Utilisateurs_Responsable ON Demandes.id_responsable = Utilisateurs_Responsable.id_utilisateur
LEFT JOIN Formations ON Demandes.id_formation = Formations.id_formation 
LEFT JOIN Formations_Demandes ON Demandes.id_formation = Formations_Demandes.id_formation 			
LEFT JOIN Plans AS Plans_Plan ON DemandesAuPlan.id_plan = Plans_plan.id_plan 
LEFT JOIN Formations AS Formations_Plan ON DemandesAuPlan.id_formation = Formations_plan.id_formation 
LEFT JOIN Utilisateurs AS Utilisateurs_Plan ON DemandesAuPlan.id_utilisateur = Utilisateurs_plan.id_utilisateur 
LEFT JOIN Utilisateurs AS Utilisateurs_Responsable_plan ON  DemandesAuPlan.id_responsable = Utilisateurs_Responsable_plan.id_utilisateur
LEFT JOIN Formations_Demandes AS Formations_Demandes_Plan ON DemandesAuPlan.id_formation = Formations_Demandes_plan.id_formation 
 
WHERE Plans.Annee = @annee 
 
UNION ALL
 
SELECT	id_demande_demande_au_plan.id AS id ,
id_demande_demande_au_plan.id_demande_reel AS id_demande_reel,			
id_demande_demande_au_plan.id_demande_au_plan AS id_demande_au_plan,														
Demandes.id_utilisateur,
Utilisateurs.nom,
Utilisateurs.prenom
 
FROM id_demande_demande_au_plan 
LEFT JOIN Demandes ON id_demande_demande_au_plan.id_demande_reel = Demandes.id_demande 
LEFT JOIN DemandesAuPlan ON id_demande_demande_au_plan.id_demande_au_plan = DemandesAuPlan.id_demande 
LEFT JOIN Plans ON Demandes.id_plan = Plans.id_plan
LEFT JOIN Utilisateurs ON Demandes.id_utilisateur = Utilisateurs.id_utilisateur 
LEFT JOIN Utilisateurs AS Utilisateurs_Responsable ON Demandes.id_responsable = Utilisateurs_Responsable.id_utilisateur
LEFT JOIN Formations ON Demandes.id_formation = Formations.id_formation 
LEFT JOIN Formations_Demandes ON Demandes.id_formation = Formations_Demandes.id_formation 			
LEFT JOIN Plans AS Plans_Plan ON DemandesAuPlan.id_plan = Plans_plan.id_plan 
LEFT JOIN Formations AS Formations_Plan ON DemandesAuPlan.id_formation = Formations_plan.id_formation 
LEFT JOIN Utilisateurs AS Utilisateurs_Plan ON DemandesAuPlan.id_utilisateur = Utilisateurs_plan.id_utilisateur 
LEFT JOIN Utilisateurs AS Utilisateurs_Responsable_plan ON  DemandesAuPlan.id_responsable = Utilisateurs_Responsable_plan.id_utilisateur
LEFT JOIN Formations_Demandes AS Formations_Demandes_Plan ON DemandesAuPlan.id_formation = Formations_Demandes_plan.id_formation 
 
WHERE Plans_plan.Annee = @annee

A +

27/06/2007, 15h56
lapanne

Merci pour cette piste mais petit souci, mes informations sur la table demandes et demandesauplan ne sont plus sur la même ligne ce qui n'est pas fondamentalement un problème mais m'obligerait à remanier le code ASP derrière. Et vu que ce code, c'est un peu le bordel, j'ai pas trop envie d'y toucher :D
28/06/2007, 08h51
SQLpro

Pourtant c'est de cette manière que vous obtiendrez un temps de réponse 100 à 1000 fois plus rapide car un OR n'est jamais optimisable, alors que deux requêtes avec un UNION, l'est...

A +
28/06/2007, 09h38
lapanne

Finalement, je suis parti avec votre solution. Je suis en train de recoder pas mal de chose. Merci beaucoup !