[SQL Server 2000] Lenteur de Proc mais pas de la requete :(

**Portekoi** · 20/11/2007, 12h51

Bonjour,

Je suis confronté à un problème bizarre.

Voici le contenu 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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
 
CREATE PROCEDURE ER_Get_By_Ageco
	@ps_Succursale as Varchar(3),
	@ps_Type_Produit as char(1),
	@ps_Login as Varchar(50),
	@ps_Type_Reseau AS Varchar(10),
	@ps_Agence As Varchar(10),
	@pi_ID_Region As Int
AS
 
	Select
		T_Date,
		A_Label As Label_1,
		'' AS Label_2,
		-1 AS ID_1,
		'' As ID_2,
		IsNull(S_Montant, 0) As Reel,
		IsNull(Objectif, 0) AS Objectif,
		-1 As The_User,
		1 As Journalier,
		IsNull(Montant_Entree_Annuel, 0) As Montant_Entree_Annuel,
		IsNull(Montant_Entree_Mensuel, 0) As Montant_Entree_Mensuel,
		IsNull(Montant_Sortie_Annuel, 0) As Montant_Sortie_Annuel,
		IsNull(Montant_Sortie_Mensuel, 0) As Montant_Sortie_Mensuel
	From
		dbo.ER_Etats
		Join dbo.ER_Etat_To_Produit On EP_ID_Etat = ET_ID
		Join dbo.ER_Produits On EP_ID_Produit = P_ID_Produit
		Join dbo.ER_Temp On T_Login = @ps_Login
		Join dbo.ER_Ageco On Replace(Replace(Replace(Replace(SubString(P_PCI, 10, Len(A_ID2)), 303, 300), 302, 300), 301, 300), 201, 200) = A_ID2
		-- ------------------------------------------
		-- Reel
		Left Join (
		Select
			A_Label As S_Ageco_Label,
			Sum(IsNull(S_Montant, 0)) As S_Montant,
			S_Date_Stock
		From
			dbo.ER_DTW_Stock
			Join dbo.ER_Produits_Label On PL_ID = S_ID_Produits
			Join dbo.ER_Ageco On A_ID2 = S_ID_Ageco
		Where
			S_Succursale = @ps_Succursale
			And S_Type_Produit = @ps_Type_Produit
			And S_Type_Reseau = @ps_Type_Reseau
			And (@pi_ID_Region = -1 Or S_ID_Region = @pi_ID_Region)
			And (S_Agence = @ps_Agence Or @ps_Agence = '')
			And S_Date_Stock In (Select T_Date From dbo.ER_Temp Where T_Login = @ps_Login)
		Group By 
			A_Label,
			S_Date_Stock
		) Reel On S_Date_Stock = T_Date And S_Ageco_Label = A_Label 
		-- Reel
		-- ------------------------------------------
		-- ---------------------
		-- Gestion des objectifs
		Left Join (
			Select
				A_Label As O_Ageco_Label,
				O_Date,
				Sum(O_Montant) as Objectif
			From
				dbo.ER_Objectifs
				Join dbo.ER_Produits_Label On PL_ID = O_ID_Produit
				Join dbo.ER_Temp On
					Year(T_Date) = Year(O_Date)
					And Month(T_Date) = Month(O_Date)
					And T_Login = @ps_Login
				Join dbo.ER_Ageco On A_ID2 = O_ID_Ageco
			Where
				O_Succursale = @ps_Succursale
				And O_Type_Produit = @ps_Type_Produit
				And O_Type_Reseau = @ps_Type_Reseau
				And (@pi_ID_Region = -1 Or O_ID_Region = @pi_ID_Region)
				And (@ps_Agence = '' Or O_Agence = @ps_Agence)
			Group by
				A_Label,
				O_Date
		) Table_Objectifs
		On
			O_Ageco_Label = A_Label 
			And Year(O_Date) = Year(S_Date_Stock)
			And Month(O_Date) = Month(S_Date_Stock)
		-- Gestion des objectifs
		-- ---------------------
 
 
 
 
 
 
 
		-- ------------------------------------
		-- Gestion des transferts inter-agences
		Left Join 
		(
			Select
				Sum(T_Montant_Annuel) As Montant_Entree_Annuel,
				Sum(T_Montant_Mensuel) As Montant_Entree_Mensuel,
				A_Label As T_Ageco_Label,
				T_Date_Transfert
			From 
				dbo.ER_DTW_Transferts
				Join dbo.ER_Produits_Label On
					PL_ID = T_Id_Produit_Destination
					And IsNull(PL_Agence, 'REZ') =
						Case T_Type_Reseau_Destination
							When 'A-I' Then T_AG_Destination
							Else 'REZ'
						End
				Join dbo.ER_Ageco On A_ID2 = T_ID_Ageco
			Where
				T_Succursale = @ps_Succursale
				And T_Type_Produit = @ps_Type_Produit
				And (@pi_ID_Region = -1 Or T_ID_Region_Destination = @pi_ID_Region)
				And T_Date_Transfert In (Select Max(T_Date) From dbo.ER_Temp Where T_Login = @ps_Login)
			Group By
				A_Label,
				T_Date_Transfert
		) Entrees
		On
			Entrees.T_Ageco_Label = A_Label 
			And Year(Entrees.T_Date_Transfert) = Year(S_Date_Stock)
			And Month(Entrees.T_Date_Transfert) = Month(S_Date_Stock)
		-- Gestion des transferts inter-agences
		-- ------------------------------------
 
 
 
		-- ------------------------------------
		-- Gestion des transferts inter-agences
		Left Join 
		(
			Select
				Sum(T_Montant_Annuel) As Montant_Sortie_Annuel,
				Sum(T_Montant_Mensuel) As Montant_Sortie_Mensuel,
				A_Label As T_Ageco_Label,
				T_Date_Transfert
			From 
				dbo.ER_DTW_Transferts
				Join dbo.ER_Produits_Label On
					PL_ID = T_Id_Produit_Origine
					And IsNull(PL_Agence, 'REZ') =
						Case T_Type_Reseau_Origine
							When 'A-I' Then T_AG_Origine
							Else 'REZ'
						End
				Join dbo.ER_Ageco On A_ID2 = T_ID_Ageco
			Where
				T_Succursale = @ps_Succursale
				And T_Type_Produit = @ps_Type_Produit
				And (@pi_ID_Region = -1 Or T_ID_Region_Origine = @pi_ID_Region)
				And T_Date_Transfert In (Select Max(T_Date) From dbo.ER_Temp Where T_Login = @ps_Login)
			Group By
				A_Label,
				T_Date_Transfert
		) Sorties
		On
			Sorties.T_Ageco_Label = A_Label 
			And Year(Sorties.T_Date_Transfert) = Year(S_Date_Stock)
			And Month(Sorties.T_Date_Transfert) = Month(S_Date_Stock)
		-- Gestion des transferts inter-agences
		-- ------------------------------------
	Where
 		P_Type_Produit = @ps_Type_Produit
		And ET_Type_Reseau = @ps_Type_Reseau
	Group By
		T_Date, A_Label, A_Ordre, Objectif, S_Montant,
		Montant_Entree_Annuel,
		Montant_Entree_Mensuel,
		Montant_Sortie_Annuel,
		Montant_Sortie_Mensuel
	Order By
		A_Ordre, T_Date
 
GO

Si je fais ceci dans l'analyser de query :
Execute dbo.ER_Get_By_Ageco @ps_Succursale = 'FR', @ps_Type_Produit = 'E' , @ps_Type_Reseau = 'REZ', @ps_Login = 'Mon_Login', @ps_Agence = '', @pi_ID_Region = -1

La durée est de 53 secondes en moyenne.

Par contre, si j'exécute la requête directement dans l'analyser de query comme ceci :

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
 
DECLARE @ps_Succursale as Varchar(3)
DECLARE @ps_Type_Produit as char(1)
DECLARE @ps_Login as Varchar(50)
DECLARE @ps_Type_Reseau AS Varchar(10)
DECLARE @ps_Agence As Varchar(10)
DECLARE @pi_ID_Region As Int
 
SET @ps_Succursale = 'FR'
SET @ps_Type_Produit = 'E'
SET @ps_Type_Reseau = 'REZ'
SET @ps_Login = 'Mon_Login'
SET @ps_Agence = ''
SET @pi_ID_Region = -1 
 
	Select
		T_Date,
		A_Label As Label_1,
		'' AS Label_2,
		-1 AS ID_1,
		'' As ID_2,
		IsNull(S_Montant, 0) As Reel,
		IsNull(Objectif, 0) AS Objectif,
		-1 As The_User,
		1 As Journalier,
		IsNull(Montant_Entree_Annuel, 0) As Montant_Entree_Annuel,
		IsNull(Montant_Entree_Mensuel, 0) As Montant_Entree_Mensuel,
		IsNull(Montant_Sortie_Annuel, 0) As Montant_Sortie_Annuel,
		IsNull(Montant_Sortie_Mensuel, 0) As Montant_Sortie_Mensuel
	From
		dbo.ER_Etats
		Join dbo.ER_Etat_To_Produit On EP_ID_Etat = ET_ID
		Join dbo.ER_Produits On EP_ID_Produit = P_ID_Produit
		Join dbo.ER_Temp On T_Login = @ps_Login
		Join dbo.ER_Ageco On Replace(Replace(Replace(Replace(SubString(P_PCI, 10, Len(A_ID2)), 303, 300), 302, 300), 301, 300), 201, 200) = A_ID2
		-- ------------------------------------------
		-- Reel
		Left Join (
		Select
			A_Label As S_Ageco_Label,
			Sum(IsNull(S_Montant, 0)) As S_Montant,
			S_Date_Stock
		From
			dbo.ER_DTW_Stock
			Join dbo.ER_Produits_Label On PL_ID = S_ID_Produits
			Join dbo.ER_Ageco On A_ID2 = S_ID_Ageco
		Where
			S_Succursale = @ps_Succursale
			And S_Type_Produit = @ps_Type_Produit
			And S_Type_Reseau = @ps_Type_Reseau
			And (@pi_ID_Region = -1 Or S_ID_Region = @pi_ID_Region)
			And (S_Agence = @ps_Agence Or @ps_Agence = '')
			And S_Date_Stock In (Select T_Date From dbo.ER_Temp Where T_Login = @ps_Login)
		Group By 
			A_Label,
			S_Date_Stock
		) Reel On S_Date_Stock = T_Date And S_Ageco_Label = A_Label 
		-- Reel
		-- ------------------------------------------
		-- ---------------------
		-- Gestion des objectifs
		Left Join (
			Select
				A_Label As O_Ageco_Label,
				O_Date,
				Sum(O_Montant) as Objectif
			From
				dbo.ER_Objectifs
				Join dbo.ER_Produits_Label On PL_ID = O_ID_Produit
				Join dbo.ER_Temp On
					Year(T_Date) = Year(O_Date)
					And Month(T_Date) = Month(O_Date)
					And T_Login = @ps_Login
				Join dbo.ER_Ageco On A_ID2 = O_ID_Ageco
			Where
				O_Succursale = @ps_Succursale
				And O_Type_Produit = @ps_Type_Produit
				And O_Type_Reseau = @ps_Type_Reseau
				And (@pi_ID_Region = -1 Or O_ID_Region = @pi_ID_Region)
				And (@ps_Agence = '' Or O_Agence = @ps_Agence)
			Group by
				A_Label,
				O_Date
		) Table_Objectifs
		On
			O_Ageco_Label = A_Label 
			And Year(O_Date) = Year(S_Date_Stock)
			And Month(O_Date) = Month(S_Date_Stock)
		-- Gestion des objectifs
		-- ---------------------
 
 
 
 
 
 
 
		-- ------------------------------------
		-- Gestion des transferts inter-agences
		Left Join 
		(
			Select
				Sum(T_Montant_Annuel) As Montant_Entree_Annuel,
				Sum(T_Montant_Mensuel) As Montant_Entree_Mensuel,
				A_Label As T_Ageco_Label,
				T_Date_Transfert
			From 
				dbo.ER_DTW_Transferts
				Join dbo.ER_Produits_Label On
					PL_ID = T_Id_Produit_Destination
					And IsNull(PL_Agence, 'REZ') =
						Case T_Type_Reseau_Destination
							When 'A-I' Then T_AG_Destination
							Else 'REZ'
						End
				Join dbo.ER_Ageco On A_ID2 = T_ID_Ageco
			Where
				T_Succursale = @ps_Succursale
				And T_Type_Produit = @ps_Type_Produit
				And (@pi_ID_Region = -1 Or T_ID_Region_Destination = @pi_ID_Region)
				And T_Date_Transfert In (Select Max(T_Date) From dbo.ER_Temp Where T_Login = @ps_Login)
			Group By
				A_Label,
				T_Date_Transfert
		) Entrees
		On
			Entrees.T_Ageco_Label = A_Label 
			And Year(Entrees.T_Date_Transfert) = Year(S_Date_Stock)
			And Month(Entrees.T_Date_Transfert) = Month(S_Date_Stock)
		-- Gestion des transferts inter-agences
		-- ------------------------------------
 
 
 
		-- ------------------------------------
		-- Gestion des transferts inter-agences
		Left Join 
		(
			Select
				Sum(T_Montant_Annuel) As Montant_Sortie_Annuel,
				Sum(T_Montant_Mensuel) As Montant_Sortie_Mensuel,
				A_Label As T_Ageco_Label,
				T_Date_Transfert
			From 
				dbo.ER_DTW_Transferts
				Join dbo.ER_Produits_Label On
					PL_ID = T_Id_Produit_Origine
					And IsNull(PL_Agence, 'REZ') =
						Case T_Type_Reseau_Origine
							When 'A-I' Then T_AG_Origine
							Else 'REZ'
						End
				Join dbo.ER_Ageco On A_ID2 = T_ID_Ageco
			Where
				T_Succursale = @ps_Succursale
				And T_Type_Produit = @ps_Type_Produit
				And (@pi_ID_Region = -1 Or T_ID_Region_Origine = @pi_ID_Region)
				And T_Date_Transfert In (Select Max(T_Date) From dbo.ER_Temp Where T_Login = @ps_Login)
			Group By
				A_Label,
				T_Date_Transfert
		) Sorties
		On
			Sorties.T_Ageco_Label = A_Label 
			And Year(Sorties.T_Date_Transfert) = Year(S_Date_Stock)
			And Month(Sorties.T_Date_Transfert) = Month(S_Date_Stock)
		-- Gestion des transferts inter-agences
		-- ------------------------------------
	Where
 		P_Type_Produit = @ps_Type_Produit
		And ET_Type_Reseau = @ps_Type_Reseau
	Group By
		T_Date, A_Label, A_Ordre, Objectif, S_Montant,
		Montant_Entree_Annuel,
		Montant_Entree_Mensuel,
		Montant_Sortie_Annuel,
		Montant_Sortie_Mensuel
	Order By
		A_Ordre, T_Date
 
GO

la requête met 1 seconde...

Une idée?

EDITION : Le serveur semble mettre 52 secondes pour faire le CREATE et 1 secondes pour la requete. Je comprends plus rien

Merci

Portekoi

**kagemaru** · 20/11/2007, 13h05

La requête est très complexe

et repose sur bcp de paramètres, en fonction de ceux-ci, le plan calculé à la 1ere exécution peut ne pas coller avec les exécutions suivantes....

Est-ce que cette exécution est la 1ere après création de la proc ?

Execute dbo.ER_Get_By_Ageco @ps_Succursale = 'FR', @ps_Type_Produit = 'E' , @ps_Type_Reseau = 'REZ', @ps_Login = 'Mon_Login', @ps_Agence = '', @pi_ID_Region = -1

Essaye en ajoutant un WITH RECOMPILE dans le CREATE PROC :

Code :

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

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE ER_Get_By_Ageco
	@ps_Succursale AS Varchar(3),
	@ps_Type_Produit AS char(1),
	@ps_Login AS Varchar(50),
	@ps_Type_Reseau AS Varchar(10),
	@ps_Agence AS Varchar(10),
	@pi_ID_Region AS Int
WITH RECOMPILE
AS .....

**Portekoi** · 20/11/2007, 13h11

Merci pour ta réponse

Je ne comprends pas ta question "Est-ce que cette exécution est la 1ere après création de la proc ?"

La procédure stockée est crée ainsi. Je l'ai supprimée et recréer puis re-exécuter mais rien à faire.

Si j'exécute plusieurs fois la requête seule, je n'ai aucun soucis de lenteur.

Pige po

**kagemaru** · 20/11/2007, 13h13

La première exécution de la proc nécessite le calcul du plan, ce qui implique une durée plus longue.
Si tu exécutes plusieurs fois la procédure d'affilée, est-ce que tu as toujours ce temps de 53 s ?

**Portekoi** · 20/11/2007, 13h14

Oui

**kagemaru** · 20/11/2007, 13h22

Mais pour être clair à 100%, on est bien d'accord que tu ne supprimes pas la proc et la recrée avant chaque exécution ? Une fois que la proc est créée, il y a juste l'Exec à faire une 1ere fois pour le calcul du plan et ensuite sql server utilise le plan calculé pour aller plus vite ...

Sinon tu dois sûrement avoir un plan d'exécution qui différe entre ton sql dynamique et ta proc, c'est plutôt rare.

**Portekoi** · 20/11/2007, 14h27

Oui, je ne la supprime jamais et franchement, c'est bien la première fois que cela m'arrive.

Cela marchait encore hier ou avant-hier et je n'ai fais aucune modification, ni sur la base, ni sur le serveur

Peut être qu'il se met en grève....

**Monstros Velu** · 20/11/2007, 14h34

Et tu as le même résultat au moins ? ;o)

**Portekoi** · 20/11/2007, 14h37

ben heureusement, au bout de 53 secondes lol.

Je peux même prendre mon café ^_^

Allez quoi, une ch'tiote idée?

**SQLpro** · 20/11/2007, 17h11

Un petit coup d'UPDATE STATISTICS serait peut être bienvennu.

Pensez aussi à défragmenter vos index soit en DBCC INDEX DEFRAG, soit en DBREINDEX.

A +

[SQL Server 2000] Lenteur de Proc mais pas de la requete :(

Développement SQL Server

Discussions similaires

Partager

Partager