[Procedure Stockée] Optimisation

**lapanne** · 27/06/2007, 11h46

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

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 :

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
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

**rudib** · 27/06/2007, 11h55

Bonjour,

Peux-tu faire un

Code :

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

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/

**lapanne** · 27/06/2007, 12h59

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

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

Le fichier excel tout moche

**serge0934** · 27/06/2007, 13h53

ce sont tes appels a la fonction isnull dans les cas

Code :

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

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

**lapanne** · 27/06/2007, 14h01

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

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

Edit : en mettant un simple count(*) à la place de mes colonnes, je tombe à 24 secondes ce qui n'est pas vraiment flagrant comme delta

**David.V** · 27/06/2007, 14h09

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.

**rudib** · 27/06/2007, 14h21

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 ?

**lapanne** · 27/06/2007, 14h26

y'a un lien vers un fichier excel

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 :

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
 
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 ?

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

**rudib** · 27/06/2007, 14h50

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
??

**lapanne** · 27/06/2007, 15h14

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.

**SQLpro** · 27/06/2007, 15h34

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

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
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 +

**lapanne** · 27/06/2007, 15h56

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

**SQLpro** · 28/06/2007, 08h51

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 +

**lapanne** · 28/06/2007, 09h38

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

[Procedure Stockée] Optimisation

MS SQL Server

Discussions similaires

Partager

Partager