Optimisation Procédure Stockée [Résolu]

neojeff · 14/06/2011, 11h39

Bonjour, je souhaite remonter des informations présentent dans une base de donnée Access vers notre serveur SQL. pour ce faire j'utilise le code ci-dessous.

Toutefois je trouve l'ensemble plutôt lent. Ces la première fois que j'utilise Transact-SQL. Si quelqu'un pouvez me corriger.

Cdt Jean-François

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
 
	/* On supprimer le lien avec le serveur LullyResultat */
EXEC sp_dropserver 'LullyResultat', 'droplogins'
GO
 
	/* On creer un lien avec la base de donnée ACCESS */
EXEC sp_addlinkedserver 
   @server = N'LullyResultat', 
   @provider = N'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = N'OLE DB Provider for Jet',
   @datasrc = N'D:\SQL-Micrologic\4F\resultatlully.mdb';
GO
 
Print 'Debut de déclaration des variables'
 
DECLARE @Adatemarq AS datetime
DECLARE @Anumserie AS int
DECLARE @Anumuv AS int
DECLARE @Adateres AS char(25)
DECLARE @Aresultat AS int
DECLARE @Atestdef AS int
DECLARE @Aindexres AS int
DECLARE @Tindexres AS int
DECLARE @Tnumtest AS int
DECLARE @Tresultat AS int
DECLARE @Tval1 AS float
DECLARE @Tval2 AS float
DECLARE @Tval3 AS float
DECLARE @Tval4 AS float
DECLARE @Tval5 AS float
DECLARE @Tval6 AS float
DECLARE @Tval7 AS float
DECLARE @Tval8 AS float
DECLARE @Tval9 AS float
DECLARE @Tval10 AS float
DECLARE @Tval11 AS float
DECLARE @Tval12 AS float
DECLARE @Tval13 AS float
DECLARE @OldNumSerie AS int
 
	-- Delclaration du curseur, avec ça requéte
DECLARE MyCursor CURSOR FOR
	SELECT Appareil.datemarq, Appareil.numserie, Appareil.numuv, Appareil.dateres, Appareil.resultat, Appareil.testdef, 
		Appareil.indexres, TEST.indexres, TEST.numtest, TEST.resultat, TEST.val1, TEST.val2, TEST.val3, 
		TEST.val4, TEST.val5, TEST.val6, TEST.val7, TEST.val8, TEST.val9, TEST.val10, TEST.val11, TEST.val12, TEST.val13
		FROM LullyResultat...Appareil LEFT JOIN LullyResultat...TEST ON Appareil.indexres = TEST.indexres 
		WHERE Appareil.numserie>0 AND Appareil.numuv>0 AND Appareil.datemarq>='20/05/2011' ORDER BY Appareil.numserie, Appareil.datemarq
 
		-- ouverture du curseur
    OPEN MyCursor 
 
	-- Initialisation du numero de serie
SET @OldNumSerie=0
 
	-- On remplie avec les premiéres données reçus
FETCH  MyCursor INTO @Adatemarq, @Anumserie, @Anumuv, @Adateres, @Aresultat, @Atestdef, @Aindexres, @Tindexres, @Tnumtest,
					@Tresultat, @Tval1, @Tval2, @Tval3, @Tval4, @Tval5, @Tval6, @Tval7, @Tval8, @Tval9, @Tval10, @Tval11, @Tval12, @Tval13
 
Print 'Début de la boucle'
WHILE @@fetch_Status = 0
	BEGIN
			-- si le ces un nouveau produit
		IF @OldNumSerie != @Anumserie
			BEGIN
					-- Memorise le numero de serie
				SET @OldNumSerie = @Anumserie
 
					-- Insertion du produit dans la table declencheurs
				INSERT INTO [WXFR69123D\SQLEXPRESS].[Lully].[dbo].Declencheurs 
							([Date], [N_Serie], [N_UV], [res1], [res2], [res3], [res4], [res5], [res6], [res7], [res8], [res9], [res10],
									[res11], [res12], [res13], [res14], [res15], [res16], [res17], [res18], [res19], [res20], [pris_en_compte]) 
							VALUES (CAST(RIGHT(@Adateres,21) AS DATETIME), @Anumserie, @Anumuv, @Aresultat, @Atestdef, 0, @Anumuv, 
									0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1)
			END
 
			-- Insertion des controles liés au produit dans la table TESTS
		IF @Aresultat=2 AND @Atestdef = @Tnumtest
				-- Test Defectueux
			INSERT INTO [WXFR69123D\SQLEXPRESS].[Lully].[dbo].TESTS 
							([Date], [N_Serie], [N_UV], [N_TEST], [N_Testeur], [pris_en_compte], [Perturbant], 
							[res1], [res2], [res3], [res4], [res5], [res6], [res7], [res8], [res9], [res10],
									[res11], [res12], [res13], [res14], [res15], [res16], [res17], [res18], [res19], [res20]) 
							VALUES (CAST(RIGHT(@Adateres,21) AS DATETIME), @Anumserie, @Anumuv, @Tnumtest, 1, 0, 0, 2, @Tval1, @Tval2, @Tval3, @Tval4,
									@Tval5, @Tval6, @Tval7, @Tval8, @Tval9, @Tval10, @Tval11, @Tval12, @Tval13, 0, 0, 0, 0,  @TResultat, 0)
		Else
				-- Test bon
			INSERT INTO [WXFR69123D\SQLEXPRESS].[Lully].[dbo].TESTS 
							([Date], [N_Serie], [N_UV], [N_TEST], [N_Testeur], [pris_en_compte], [Perturbant], 
							[res1], [res2], [res3], [res4], [res5], [res6], [res7], [res8], [res9], [res10],
									[res11], [res12], [res13], [res14], [res15], [res16], [res17], [res18], [res19], [res20]) 
							VALUES (CAST(RIGHT(@Adateres,21) AS DATETIME), @Anumserie, @Anumuv, @Tnumtest, 1, 0, 0, 1, @Tval1, @Tval2, @Tval3, @Tval4,
									@Tval5, @Tval6, @Tval7, @Tval8, @Tval9, @Tval10, @Tval11, @Tval12, @Tval13, 0, 0, 0, 0,  @TResultat, 0)
 
			-- Données suivantes
		FETCH MyCursor INTO @Adatemarq, @Anumserie, @Anumuv, @Adateres, @Aresultat, @Atestdef, @Aindexres, @Tindexres, 
							@Tnumtest, @Tresultat, @Tval1, @Tval2, @Tval3, @Tval4, @Tval5, @Tval6, @Tval7, 
							@Tval8, @Tval9, @Tval10, @Tval11, @Tval12, @Tval13
	END
 
	-- Fermeture du curseur, libération de la memoire
CLOSE myCursor
DEALLOCATE myCursor
 
Print '----------------- Terminé ------------------------'

elsuket · 14/06/2011, 12h04

Bonjour,

Je passe sur les serveurs liés.

Oubliez les curseurs quand vous programmez en SQL.
Ici avec 3 requêtes on fait la même chose, d'un seul tir :

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
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
-- Insertion des nouveaux produits dans la table declencheurs
INSERT INTO	[WXFR69123D\SQLEXPRESS].Lully.dbo.Declencheurs 
(
	Date
	, N_Serie
	, N_UV
	, res1
	, res2
	, res3
	, res4
	, res5
	, res6
	, res7
	, res8
	, res9
	, res10
	, res11
	, res12
	, res13
	, res14
	, res15
	, res16
	, res17
	, res18
	, res19
	, res20
	, pris_en_compte
) 
SELECT		Date
		, S.numserie
		, S.numuv
		, S.resultat
		, S.testdef
		, 0
		, S.numuv
		, 0
		, 0
		, 0
		, 0
		, 0
		, 0
		, 0
		, 0
		, 1
		, 0
		, 0
		, 0
		, 0
		, 0
		, 0
		, 0
		, 1
FROM		(
			SELECT		CAST(RIGHT(A.dateres, 21) AS DATETIME) AS Date
					, A.numserie
					, A.numuv
					, A.resultat
					, A.testdef
					, ROW_NUMBER() OVER(PARTITION BY A.numserie ORDER BY A.numserie, A.datemarq) AS rang
			FROM		LullyResultat...Appareil AS A
			LEFT JOIN	LullyResultat...TEST AS T 
						ON A.indexres = T.indexres 
			WHERE		A.numserie > 0
			AND		A.numuv > 0
			AND		A.datemarq >= '20110520'
 
		) AS S
WHERE		S.rang = 1
 
-- Insertion des controles liés au produit dans la table TESTS. -- Test Defectueux			
INSERT INTO [WXFR69123D\SQLEXPRESS].Lully.dbo.TESTS 
(
	Date
	, N_Serie
	, N_UV
	, N_TEST
	, N_Testeur
	, pris_en_compte
	, Perturbant
	, res1
	, res2
	, res3
	, res4
	, res5
	, res6
	, res7
	, res8
	, res9
	, res10
	, res11
	, res12
	, res13
	, res14
	, res15
	, res16
	, res17
	, res18
	, res19
	, res20
)
SELECT		CAST(RIGHT(A.dateres,21) AS DATETIME)
		, A.numserie
		, A.numuv
		, T.numtest
		, 1
		, 0
		, 0
		, 2
		, T.val1
		, T.val2
		, T.val3
		, T.val4
		, T.val5
		, T.val6
		, T.val7
		, T.val8
		, T.val9
		, T.val10
		, T.val11
		, T.val12
		, T.val13
		, 0
		, 0
		, 0
		, 0
		, T.Resultat
		, 0
FROM		LullyResultat...Appareil AS A
LEFT JOIN	LullyResultat...TEST AS T 
			ON A.indexres = T.indexres 
			AND A.testdef = T.numtest		
			AND A.resultat = 2
WHERE		A.numserie > 0
AND		A.numuv > 0
AND		A.datemarq >= '20110520'
 
-- Test bon
INSERT INTO [WXFR69123D\SQLEXPRESS].Lully.dbo.TESTS 
(
	Date
	, N_Serie
	, N_UV
	, N_TEST
	, N_Testeur
	, pris_en_compte
	, Perturbant
	, res1
	, res2
	, res3
	, res4
	, res5
	, res6
	, res7
	, res8
	, res9
	, res10
	, res11
	, res12
	, res13
	, res14
	, res15
	, res16
	, res17
	, res18
	, res19
	, res20
)
SELECT		CAST(RIGHT(A.dateres,21) AS DATETIME)
		, A.numserie
		, A.numuv
		, T.numtest
		, 1
		, 0
		, 0
		, 2
		, T.val1
		, T.val2
		, T.val3
		, T.val4
		, T.val5
		, T.val6
		, T.val7
		, T.val8
		, T.val9
		, T.val10
		, T.val11
		, T.val12
		, T.val13
		, 0
		, 0
		, 0
		, 0
		, T.Resultat
		, 0
FROM		LullyResultat...Appareil AS A
LEFT JOIN	LullyResultat...TEST AS T 
			ON A.indexres = T.indexres 
			AND A.testdef = T.numtest		
			AND A.resultat <> 2
WHERE		A.numserie > 0
AND		A.numuv > 0
AND		A.datemarq >= '20110520'

SQL est un langage ensembliste, il est donc conçu pour traiter les données dans leur ensemble et pas ligne à ligne, comme vous le faites avec un curseur.

@++

neojeff · 14/06/2011, 13h17

Trés bien je test ça cette aprés midi.

Cdt Jean-François

neojeff · 15/06/2011, 17h24

Trés bien tous fonctionne à merveille.

Ci-dessous le code mise en oeuvre.

Merci pour votre aide.

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
 
	/* On supprimer le lien avec le serveur LullyResultat */
EXEC sp_dropserver 'LullyResultat', 'droplogins'
GO
 
	/* On creer un lien avec la base de donnée ACCESS */
EXEC sp_addlinkedserver 
   @server = N'LullyResultat', 
   @provider = N'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = N'OLE DB Provider for Jet',
   @datasrc = N'D:\SQL-Micrologic\4F\resultatlully.mdb';
GO
 
	--Remplissage de la table Declencheurs (1 ligne par produit)
INSERT INTO	[WXFR69123D\SQLEXPRESS].Lully.dbo.Declencheurs 
	(Date, N_Serie, N_UV, 
	res1, res2, res3, 
	res4, res5, res6, 
	res7, res8, res9, 
	res10, res11, res12, 
	res13, res14, res15, 
	res16, res17, res18, 
	res19, res20, pris_en_compte)
	SELECT 
		CAST(RIGHT(A.dateres,19) AS DATETIME), A.numserie, A.numuv, 
		A.resultat, A.testdef, 0, 
		A.numuv,0,0,
		0,0,0,
		0,0,0,
		1,0,0,
		0,0,0,
		0,0,1
	FROM LullyResultat...Appareil AS A WHERE A.numserie>0 AND A.numuv>0 ORDER BY A.numserie, A.dateres
 
 
	--Remplissage de la table Tests avec les Tests Bon (1 ligne par Test bon)
INSERT INTO	[WXFR69123D\SQLEXPRESS].Lully.dbo.TESTS
	(Date, N_Serie, N_UV,
	N_Test, N_Testeur, Pris_en_compte, Perturbant,
	res1, res2, res3, 
	res4, res5, res6, 
	res7, res8, res9, 
	res10, res11, res12, 
	res13, res14, res15, 
	res16, res17, res18, 
	res19, res20)
	SELECT 
		CAST(RIGHT(A.dateres,19) AS DATETIME), A.numserie, A.numuv,
		T.numtest, A.resultat, 0, 0, 
		T.val1, T.val2, T.val3, 
		T.val4, T.val5, T.val6, 
		T.val7, T.val8, T.val9, 
		T.val10, T.val11, T.val12, 
		T.val13, 0, 0, 
		0, 0, 0,
		CASE T.resultat
			WHEN 1 THEN 0 
            ELSE T.resultat
		END, 0
	FROM LullyResultat...Appareil AS A LEFT JOIN LullyResultat...TEST AS T ON A.indexres = T.indexres 
	WHERE A.numserie>0 AND A.numuv>0 AND T.numtest>0 ORDER BY A.numserie, A.dateres
 
	/* On supprimer le lien avec le serveur LullyResultat */
EXEC sp_dropserver 'LullyResultat', 'droplogins'
GO

14/06/2011, 13h17	#3
neojeff Invité de passage Inscription : juin 2011 Messages : 14 Détails du profil Informations personnelles : Sexe : Localisation : France Informations forums : Inscription : juin 2011 Messages : 14 Points : 2 Points : 2	Trés bien je test ça cette aprés midi. Cdt Jean-François
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email