Discussion :

[Portekoi]

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