Plan d'exécution d'un requête [Résolu]

[Etienne5685]

Bonjour,

lors d' un traitement sql j'ai deux requêtes de contrôle qui vont marquer un champ d'une table à ok pour ou à CI, les lignes dont ce champ est à Ok vont être insérées dans une autre table puis historiser dans dans un table d'histo.

ce contrôle est fait par deux requête update IDENTIQUE la première avec des jointures vers 6 tables environs 2 millions de lignes chacunes. la deuxième vers une seul table avec 500000 lignes

Mon problème est le suivant le deuxième update prend 50% du temps du traitement en regardant le plan d'exécution de cette requête il y un trie qui prend 86% du temps de la requête

voici la requête en question:

Code :

1
2
3
4
5
6
7
8
9
10
11
 
UPDATE s SET SAS_STATUT=case
  when 1=0 then ''
  when SAS_STATUT = 'CI' then 'CI'
  when rg04.ID_PERS_SRC IS NULL then 'CI'-- CG1 (une ligne par CI)
  else 'OK' end,SAS_ERREUR=substring(isnull(SAS_ERREUR,'')
  +case when rg04.ID_PERS_SRC IS NULL then '[erreur de CI sur DWH_PERS]"'+isnull(convert(varchar,s.ID_PERS_SRC),'')+'" ;' else '' end -- CG1 (une ligne par CI)
 
  ,1,8000) FROM [dbo].SAS_SHIPPING_LINE s INNER JOIN #TMP_SHIPPING_LINE t on s.SAS_ID=t.SAS_ID
  LEFT JOIN [dbo].DWH_PERS rg04  ON rg04.ID_PERS_SRC = s.ID_PERS_SRC AND rg04.CD_BRAND=s.CD_BRAND AND rg04.CD_COUNTRY=s.CD_COUNTRY -- CG1 (une ligne par CG)
  WHERE t.RANG=@compteur AND (s.SAS_STATUT IS NULL OR s.SAS_STATUT <> 'KO')

Pourquoi fait il un trie aussi consommateur
pourquoi le premier update identique et plus "lourd" (6 jointure vers des tables plus importantes) ne prend que 2 % du temps du traitement

[mikedavem]

Est ce que votre première requête contient des tables temporaires ?
Je vois que la deuxième en contient une ?

On pourrait avoir les 2 requêtes et les plans d'exécutions ?

++

[Etienne5685]

Voici la première requête ainsi que les sqlplan en pj

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
 
UPDATE s SET SAS_STATUT=case
  when 1=0 then ''
  when ri21.TYP_SHIP IS NULL then 'CI'-- CI1 (une ligne par CI)
  when ri18.CD_CANCEL IS NULL  AND s.CD_CANCEL IS NOT NULL then 'CI'-- CI2 (une ligne par CI)
  when rg08.ID_ORD_LINE_SRC IS NULL then 'CI'-- CG2 (une ligne par CI)
  when rg07.ID_PACK_SRC IS NULL then 'CI'-- CG3 (une ligne par CI)
  when rg09.ID_PRD_SRC IS NULL then 'CI'-- CG4(une ligne par CI)
 
 
  else 'OK' end,SAS_ERREUR=substring(''
  +case when ri21.TYP_SHIP IS NULL then '[erreur de CI sur DWH_REF_TYPE_SHIP_LINE]"'+isnull(convert(varchar,s.TYP_SHIP),'')+'" ;' else '' end -- CI1 (une ligne par CI)
  +case when ri18.CD_CANCEL IS NULL AND s.CD_CANCEL IS NOT NULL  then '[erreur de CI sur DWH_REF_CANCEL_CODE]"'+isnull(convert(varchar,s.CD_CANCEL),'')+'" ;' else '' end -- CI2 (une ligne par CI)  
  +case when rg08.ID_ORD_LINE_SRC IS NULL then '[erreur de CI sur DWH_ORDER_LINE]"'+isnull(convert(varchar,s.ID_ORD_LINE_SRC),'')+'" ;' else '' end -- CG2 (une ligne par CI)
  +case when rg07.ID_PACK_SRC IS NULL then '[erreur de CI sur DWH_PACKAGE]"'+isnull(convert(varchar,s.ID_PACK_SRC),'')+'" ;' else '' end -- CG3 (une ligne par CI)
  +case when rg09.ID_PRD_SRC IS NULL then '[erreur de CI sur DWH_PRODUCT]"'+isnull(convert(varchar,s.ID_PRD_SRC),'')+'" ;' else '' end -- CG4 (une ligne par CI)
  ,1,8000) FROM [dbo].SAS_SHIPPING_LINE s INNER JOIN #TMP_SHIPPING_LINE t on s.SAS_ID=t.SAS_ID
  LEFT JOIN [dbo].DWH_REF_TYPE_SHIP_LINE ri21 ON ri21.TYP_SHIP=s.TYP_SHIP -- CI1 (une ligne par CI)
												AND ri21.CD_BRAND=s.CD_BRAND
												AND ri21.CD_COUNTRY=s.CD_COUNTRY
												AND ri21.CD_BUSLINE=s.CD_BUSLINE
  LEFT JOIN [dbo].DWH_REF_CANCEL_CODE ri18 ON ri18.CD_CANCEL=s.CD_CANCEL -- CI2 (une ligne par CI)
												AND ri18.CD_BRAND=s.CD_BRAND
												AND ri18.CD_COUNTRY=s.CD_COUNTRY
												AND ri18.CD_BUSLINE=s.CD_BUSLINE
 
 
 
  LEFT  JOIN [dbo].DWH_ORDER_LINE rg08 ON rg08.ID_ORD_LINE_SRC = s.ID_ORD_LINE_SRC AND rg08.CD_BRAND=s.CD_BRAND AND rg08.CD_COUNTRY=s.CD_COUNTRY AND rg08.CD_BUSLINE=s.CD_BUSLINE -- CG2 (une ligne par CG)
  LEFT  JOIN [dbo].DWH_PACKAGE rg07 ON rg07.ID_PACK_SRC = s.ID_PACK_SRC AND rg07.CD_BRAND=s.CD_BRAND AND rg07.CD_COUNTRY=s.CD_COUNTRY  AND rg07.CD_BUSLINE=s.CD_BUSLINE-- CG3 (une ligne par CG)
 LEFT  JOIN [dbo].DWH_PRODUCT rg09 ON rg09.ID_PRD_SRC = s.ID_PRD_SRC AND rg09.CD_BRAND=s.CD_BRAND AND rg09.CD_COUNTRY=s.CD_COUNTRY  AND rg09.CD_BUSLINE=s.CD_BUSLINE-- CG4 (une ligne par CG)
  WHERE t.RANG=@compteur AND (s.SAS_STATUT IS NULL OR s.SAS_STATUT <> 'KO')

[iberserk]

Bonne question de mikedavem, si la première n'en contient pas cherchez de ce côté là en priorité...

Au passage ce que vous faites semble extrêmement coûteux et est le résultat d'une modélisation bancale ce qui entraîne des surcoûts comme l'extraction des chaines dans le case (substring etc.).

[iberserk]

A nos message se sont croisés....

[Etienne5685]

y a t il un moyen de savoir se qui cause le trie (comme sous oracle) ou de forcer un plan d'exécution?

[iberserk]

y a t il un moyen de savoir se qui cause le trie (comme sous oracle) ou de forcer un plan d'exécution?

Si SQL SERVER s'efforce de faire un tri de l'entrée c'est qu'il a estimé que c'était la façon la plus rapide ensuite de traiter les informations...

Il manque de toute evidence un index très important sur la table temporaire (comme il vous le dit dailleurs...).

L'ajout d'un index peut très bien supprimer ce tri si SQL SERVER se retrouve avec une entrée triée comme il faut suite à l'utilisation de ce dernier.

Essayez en créant cet index mais surtout posez vous la bonne question: pourquoi utilisez vous une table temporaire?

[Etienne5685]

merci,

Voici le code du traitement au complet:

des index sont déjà présents sur la table temp qui contient les colonnes qui forment la clé fonctionnelle

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
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
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
 
 
begin
DECLARE @compteur BIGINT
DECLARE @max_rang BIGINT
DECLARE @nb_read INT
DECLARE @nb_ins INT
DECLARE @nb_upd INT
DECLARE @nb_rejet INT
DECLARE @nb_warning INT
SELECT s.SAS_ID,
  s.ID_SHIP_LINE_SRC
,  s.TYP_SHIP
,  s.CD_BRAND
,  s.CD_COUNTRY
,  s.CD_BUSLINE
  ,'ins' mode,convert(bigint,0) DWH_ID,ROW_NUMBER() OVER(PARTITION BY
  s.ID_SHIP_LINE_SRC
,  s.TYP_SHIP
,  s.CD_BRAND
,  s.CD_COUNTRY
,  s.CD_BUSLINE
 ORDER BY DT_EXTRACT,SAS_ID
 ) RANG
  INTO #TMP_SHIPPING_LINE from [dbo].SAS_SHIPPING_LINE s
CREATE NONCLUSTERED INDEX #TIX_SHIPPING_LINE ON #TMP_SHIPPING_LINE
  (SAS_ID)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 CREATE NONCLUSTERED INDEX #TIX_SHIPPING_LINE_RANG ON #TMP_SHIPPING_LINE
  (RANG)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 
SELECT @max_rang=isnull(max(RANG),0)  FROM #TMP_SHIPPING_LINE
SET @compteur = 1
SET @nb_ins = 0
SET @nb_upd = 0
 
UPDATE [dbo].SAS_SHIPPING_LINE SET SAS_STATUT=NULL
SET @nb_read=@@ROWCOUNT
 
WHILE @compteur <= @max_rang
BEGIN
 
-- test de récence (rejet):
UPDATE s SET SAS_STATUT='KO',SAS_ERREUR='DT_EXTRACT trop ancien par rapport a celui en DWH'  FROM [dbo].SAS_SHIPPING_LINE s INNER JOIN #TMP_SHIPPING_LINE t on s.SAS_ID=t.SAS_ID 
  INNER JOIN [dbo].DWH_SHIPPING_LINE d ON 
d.ID_SHIP_LINE_SRC=s.ID_SHIP_LINE_SRC
AND d.TYP_SHIP=s.TYP_SHIP
AND d.CD_BRAND=s.CD_BRAND
AND d.CD_COUNTRY=s.CD_COUNTRY
AND d.CD_BUSLINE=s.CD_BUSLINE
  WHERE t.RANG=@compteur AND s.DT_EXTRACT<=d.DT_EXTRACT
 
 
 
 
 
-- tests de Contrainte d integrite (chapitre à ne pas effacer, meme si pas de CI):
UPDATE s SET SAS_STATUT=case
  when 1=0 then ''
  when ri21.TYP_SHIP IS NULL then 'CI'-- CI1 (une ligne par CI)
  when ri18.CD_CANCEL IS NULL  AND s.CD_CANCEL IS NOT NULL then 'CI'-- CI2 (une ligne par CI)
  when rg08.ID_ORD_LINE_SRC IS NULL then 'CI'-- CG2 (une ligne par CI)
  when rg07.ID_PACK_SRC IS NULL then 'CI'-- CG3 (une ligne par CI)
  when rg09.ID_PRD_SRC IS NULL then 'CI'-- CG4(une ligne par CI)
 
 
  else 'OK' end,SAS_ERREUR=substring(''
  +case when ri21.TYP_SHIP IS NULL then '[erreur de CI sur DWH_REF_TYPE_SHIP_LINE]"'+isnull(convert(varchar,s.TYP_SHIP),'')+'" ;' else '' end -- CI1 (une ligne par CI)
  +case when ri18.CD_CANCEL IS NULL AND s.CD_CANCEL IS NOT NULL  then '[erreur de CI sur DWH_REF_CANCEL_CODE]"'+isnull(convert(varchar,s.CD_CANCEL),'')+'" ;' else '' end -- CI2 (une ligne par CI)  
  +case when rg08.ID_ORD_LINE_SRC IS NULL then '[erreur de CI sur DWH_ORDER_LINE]"'+isnull(convert(varchar,s.ID_ORD_LINE_SRC),'')+'" ;' else '' end -- CG2 (une ligne par CI)
  +case when rg07.ID_PACK_SRC IS NULL then '[erreur de CI sur DWH_PACKAGE]"'+isnull(convert(varchar,s.ID_PACK_SRC),'')+'" ;' else '' end -- CG3 (une ligne par CI)
  +case when rg09.ID_PRD_SRC IS NULL then '[erreur de CI sur DWH_PRODUCT]"'+isnull(convert(varchar,s.ID_PRD_SRC),'')+'" ;' else '' end -- CG4 (une ligne par CI)
  ,1,8000) FROM [dbo].SAS_SHIPPING_LINE s INNER JOIN #TMP_SHIPPING_LINE t on s.SAS_ID=t.SAS_ID
  LEFT JOIN [dbo].DWH_REF_TYPE_SHIP_LINE ri21 ON ri21.TYP_SHIP=s.TYP_SHIP -- CI1 (une ligne par CI)
												AND ri21.CD_BRAND=s.CD_BRAND
												AND ri21.CD_COUNTRY=s.CD_COUNTRY
												AND ri21.CD_BUSLINE=s.CD_BUSLINE
  LEFT JOIN [dbo].DWH_REF_CANCEL_CODE ri18 ON ri18.CD_CANCEL=s.CD_CANCEL -- CI2 (une ligne par CI)
												AND ri18.CD_BRAND=s.CD_BRAND
												AND ri18.CD_COUNTRY=s.CD_COUNTRY
												AND ri18.CD_BUSLINE=s.CD_BUSLINE
 
 
 
  LEFT  JOIN [dbo].DWH_ORDER_LINE rg08 ON rg08.ID_ORD_LINE_SRC = s.ID_ORD_LINE_SRC AND rg08.CD_BRAND=s.CD_BRAND AND rg08.CD_COUNTRY=s.CD_COUNTRY AND rg08.CD_BUSLINE=s.CD_BUSLINE -- CG2 (une ligne par CG)
  LEFT  JOIN [dbo].DWH_PACKAGE rg07 ON rg07.ID_PACK_SRC = s.ID_PACK_SRC AND rg07.CD_BRAND=s.CD_BRAND AND rg07.CD_COUNTRY=s.CD_COUNTRY  AND rg07.CD_BUSLINE=s.CD_BUSLINE-- CG3 (une ligne par CG)
 LEFT  JOIN [dbo].DWH_PRODUCT rg09 ON rg09.ID_PRD_SRC = s.ID_PRD_SRC AND rg09.CD_BRAND=s.CD_BRAND AND rg09.CD_COUNTRY=s.CD_COUNTRY  AND rg09.CD_BUSLINE=s.CD_BUSLINE-- CG4 (une ligne par CG)
  WHERE t.RANG=@compteur AND (s.SAS_STATUT IS NULL OR s.SAS_STATUT <> 'KO')
 
-- tests de Contrainte d integrite (chapitre à ne pas effacer, meme si pas de CI):
UPDATE s SET SAS_STATUT=case
  when 1=0 then ''
  when SAS_STATUT = 'CI' then 'CI'
  when rg04.ID_PERS_SRC IS NULL then 'CI'-- CG1 (une ligne par CI)
  --when ri01.CD_BRAND is null then 'CI'-- CI3 (une ligne par CI)
--  when ri02.CD_COUNTRY is null then 'CI'-- CI4 (une ligne par CI)
--  when ri03.CD_BUSLINE is null then 'CI'-- CI5 (une ligne par CI)  
  else 'OK' end,SAS_ERREUR=substring(isnull(SAS_ERREUR,'')
  +case when rg04.ID_PERS_SRC IS NULL then '[erreur de CI sur DWH_PERS]"'+isnull(convert(varchar,s.ID_PERS_SRC),'')+'" ;' else '' end -- CG1 (une ligne par CI)
--+case when ri01.CD_BRAND is null then '[erreur de CI sur DWH_REF_BRAND]"'+isnull(convert(varchar,s.CD_BRAND),'')+'" ;' else '' end -- CI3 (une ligne par CI)
--  +case when ri02.CD_COUNTRY is null then '[erreur de CI sur DWH_REF_COUNTRY]"'+isnull(convert(varchar,s.CD_COUNTRY),'')+'" ;' else '' end -- CI4 (une ligne par CI)
--  +case when ri03.CD_BUSLINE is null then '[erreur de CI sur DWH_REF_BUSLINE]"'+isnull(convert(varchar,s.CD_BUSLINE),'')+'" ;' else '' end -- CI5 (une ligne par CI)
 
 
 
  ,1,8000) FROM [dbo].SAS_SHIPPING_LINE s INNER JOIN #TMP_SHIPPING_LINE t on s.SAS_ID=t.SAS_ID
  LEFT JOIN (SELECT ID_PERS_SRC,CD_BRAND,CD_COUNTRY FROM [dbo].DWH_PERS)rg04  ON rg04.ID_PERS_SRC = s.ID_PERS_SRC AND rg04.CD_BRAND=s.CD_BRAND AND rg04.CD_COUNTRY=s.CD_COUNTRY -- CG1 (une ligne par CG)
-- left join [dbo].DWH_REF_BRAND ri01 on ri01.CD_BRAND=s.CD_BRAND -- CI3 (une ligne par CI)
--  left join [dbo].DWH_REF_COUNTRY ri02 on ri02.CD_COUNTRY=s.CD_COUNTRY -- CI4 (une ligne par CI)
--  left join [dbo].DWH_REF_BUSLINE ri03 on ri03.CD_BUSLINE=s.CD_BUSLINE -- CI5 (une ligne par CI)
 
 
 
 WHERE t.RANG=@compteur AND  isnull(s.SAS_STATUT,'t')<> 'KO' 
 
 
  --
 
 
 
-- test de Presence en dwh:
UPDATE t SET mode='upd',DWH_ID=d.DWH_SHIPPING_LINE_ID FROM #TMP_SHIPPING_LINE t
  INNER JOIN [dbo].DWH_SHIPPING_LINE d ON 
d.ID_SHIP_LINE_SRC=t.ID_SHIP_LINE_SRC
AND d.TYP_SHIP=t.TYP_SHIP
AND d.CD_BRAND=t.CD_BRAND
AND d.CD_COUNTRY=t.CD_COUNTRY
AND d.CD_BUSLINE=t.CD_BUSLINE
  WHERE t.RANG=@compteur
 
 
-- archivage de dwh:
INSERT INTO DWH_SHIPPING_LINE_histo (DWH_SHIPPING_LINE_ID,
  DWH_DT_CRE,DWH_DT_MOD,
  DWH_PERS_ID,-- champ FK .(1 ligne par FK)
  DWH_ORDER_LINE_ID,-- champ FK .(1 ligne par FK)
  DWH_PACKAGE_ID,-- champ FK .(1 ligne par FK)
  DWH_PRODUCT_ID,-- champ FK .(1 ligne par FK)
  ID_SHIP_LINE_SRC,
  ID_ORD_LINE_SRC,
  ID_PACK_SRC,
  ID_PRD_SRC,
  TYP_SHIP,
  SEQ,
  DT_SHIP,
  ID_PERS_SRC,
  ID_CONTRACT,
  NB_REAL_STK,
  QTY,
  CD_CANCEL,
  D_CANCEL,
  CD_BRAND,
  CD_COUNTRY,
  CD_BUSLINE,
  DT_EXTRACT,
  DWH_DT_HISTO
  -- champ FK .(1 ligne par FK)
  ) SELECT d.DWH_SHIPPING_LINE_ID,
  d.DWH_DT_CRE,d.DWH_DT_MOD,
  d.DWH_PERS_ID, -- champ FK .(1 ligne par FK)
  d.DWH_ORDER_LINE_ID, -- champ FK .(1 ligne par FK)
  d.DWH_PACKAGE_ID, -- champ FK .(1 ligne par FK)
  d.DWH_PRODUCT_ID, -- champ FK .(1 ligne par FK)
  d.ID_SHIP_LINE_SRC,
  d.ID_ORD_LINE_SRC,
  d.ID_PACK_SRC,
  d.ID_PRD_SRC,
  d.TYP_SHIP,
  d.SEQ,
  d.DT_SHIP,
  d.ID_PERS_SRC,
  d.ID_CONTRACT,
  d.NB_REAL_STK,
  d.QTY,
  d.CD_CANCEL,
  d.D_CANCEL,
  d.CD_BRAND,
  d.CD_COUNTRY,
  d.CD_BUSLINE,
  d.DT_EXTRACT,
  GETDATE()  
  -- champ FK .(1 ligne par FK)
  FROM #TMP_SHIPPING_LINE t inner join [dbo].DWH_SHIPPING_LINE d on d.DWH_SHIPPING_LINE_ID=t.DWH_ID
  INNER JOIN [dbo].SAS_SHIPPING_LINE s ON s.SAS_ID=t.SAS_ID 
  WHERE t.RANG=@compteur AND t.mode='upd' AND s.SAS_STATUT='OK'
 
 
-- update dwh:
UPDATE d SET 
  DWH_DT_MOD=GETDATE(),
  ID_ORD_LINE_SRC=s.ID_ORD_LINE_SRC,
  ID_PACK_SRC=s.ID_PACK_SRC,
  ID_PRD_SRC=s.ID_PRD_SRC,
  SEQ=s.SEQ,
  DT_SHIP=s.DT_SHIP,
  ID_PERS_SRC=s.ID_PERS_SRC,
  ID_CONTRACT=s.ID_CONTRACT,
  NB_REAL_STK=s.NB_REAL_STK,
  QTY=s.QTY,
  CD_CANCEL=s.CD_CANCEL,
  D_CANCEL=s.D_CANCEL,
  DT_EXTRACT=s.DT_EXTRACT
  FROM [dbo].DWH_SHIPPING_LINE d
  INNER JOIN #TMP_SHIPPING_LINE t on d.DWH_SHIPPING_LINE_ID=t.DWH_ID
  INNER JOIN [dbo].SAS_SHIPPING_LINE s ON s.SAS_ID=t.SAS_ID 
  WHERE t.RANG=@compteur AND t.mode='upd' AND s.SAS_STATUT='OK'
SET @nb_upd=@nb_upd+@@ROWCOUNT
 
-- Insertion dwh:
INSERT INTO [dbo].DWH_SHIPPING_LINE (
  DWH_DT_CRE,DWH_DT_MOD,
  DWH_PERS_ID,-- champ FK a recalculer.(1 ligne par FK)
  DWH_ORDER_LINE_ID,-- champ FK a recalculer.(1 ligne par FK)
  DWH_PACKAGE_ID,-- champ FK a recalculer.(1 ligne par FK)
  DWH_PRODUCT_ID,-- champ FK a recalculer.(1 ligne par FK)
  ID_SHIP_LINE_SRC,
  ID_ORD_LINE_SRC,
  ID_PACK_SRC,
  ID_PRD_SRC,
  TYP_SHIP,
  SEQ,
  DT_SHIP,
  ID_PERS_SRC,
  ID_CONTRACT,
  NB_REAL_STK,
  QTY,
  CD_CANCEL,
  D_CANCEL,
  CD_BRAND,
  CD_COUNTRY,
  CD_BUSLINE,
  DT_EXTRACT
  -- champ FK a recalculer.(1 ligne par FK)
  ) SELECT 
  GETDATE(),GETDATE(),
  rg04.DWH_PERS_ID,-- champ FK a recalculer.(1 ligne par FK)
  rg08.DWH_ORDER_LINE_ID,-- champ FK a recalculer.(1 ligne par FK)
  rg07.DWH_PACKAGE_ID,-- champ FK a recalculer.(1 ligne par FK)
  rg09.DWH_PRODUCT_ID,-- champ FK a recalculer.(1 ligne par FK)
  s.ID_SHIP_LINE_SRC,
  s.ID_ORD_LINE_SRC,
  s.ID_PACK_SRC,
  s.ID_PRD_SRC,
  s.TYP_SHIP,
  s.SEQ,
  s.DT_SHIP,
  s.ID_PERS_SRC,
  s.ID_CONTRACT,
  s.NB_REAL_STK,
  s.QTY,
  s.CD_CANCEL,
  s.D_CANCEL,
  s.CD_BRAND,
  s.CD_COUNTRY,
  s.CD_BUSLINE,
  s.DT_EXTRACT
  -- champ FK a recalculer.(1 ligne par FK)
  FROM [dbo].SAS_SHIPPING_LINE s INNER JOIN #TMP_SHIPPING_LINE t on s.SAS_ID=t.SAS_ID
  LEFT JOIN [dbo].DWH_PERS rg04 ON rg04.ID_PERS_SRC = s.ID_PERS_SRC AND rg04.CD_BRAND=s.CD_BRAND AND rg04.CD_COUNTRY=s.CD_COUNTRY -- CG1 (une ligne par CG)
  LEFT JOIN [dbo].DWH_ORDER_LINE rg08 ON rg08.ID_ORD_LINE_SRC = s.ID_ORD_LINE_SRC AND rg08.CD_BRAND=s.CD_BRAND AND rg08.CD_COUNTRY=s.CD_COUNTRY AND rg08.CD_BUSLINE=s.CD_BUSLINE -- CG2 (une ligne par CG)
  LEFT JOIN [dbo].DWH_PACKAGE rg07 ON rg07.ID_PACK_SRC = s.ID_PACK_SRC AND rg07.CD_BRAND=s.CD_BRAND AND rg07.CD_COUNTRY=s.CD_COUNTRY  AND rg07.CD_BUSLINE=s.CD_BUSLINE-- CG3 (une ligne par CG)
  LEFT JOIN [dbo].DWH_PRODUCT rg09 ON rg09.ID_PRD_SRC = s.ID_PRD_SRC AND rg09.CD_BRAND=s.CD_BRAND AND rg09.CD_COUNTRY=s.CD_COUNTRY  AND rg09.CD_BUSLINE=s.CD_BUSLINE-- CG4 (une ligne par CG)
  WHERE t.RANG=@compteur AND t.mode='ins' AND s.SAS_STATUT='OK'
SET @nb_ins=@nb_ins+@@ROWCOUNT
 
SET @compteur = @compteur + 1
END
 
-- trace et comptage
SELECT @nb_read nb_read,@nb_ins nb_ins,@nb_upd nb_upd,
  isnull(SUM(case when s.SAS_STATUT='KO' then 1 else 0 end),0) nb_rejet, 
  isnull(SUM(case when s.SAS_STATUT='CI' then 1 else 0 end),0) nb_warning
  FROM [dbo].SAS_SHIPPING_LINE s
 
-- archivage rejet
INSERT INTO [dbo].SAS_SHIPPING_LINE_REJET (
SAS_ID,
SAS_STATUT,
SAS_ERREUR,
SAS_DT_CRE,
ID_SHIP_LINE_SRC,
ID_ORD_LINE_SRC,
ID_PACK_SRC,
ID_PRD_SRC,
TYP_SHIP,
SEQ,
DT_SHIP,
ID_PERS_SRC,
ID_CONTRACT,
NB_REAL_STK,
QTY,
CD_CANCEL,
D_CANCEL,
CD_BRAND,
CD_COUNTRY,
CD_BUSLINE,
DT_EXTRACT,
SAS_DT_REJET
) SELECT SAS_ID,SAS_STATUT,SAS_ERREUR,SAS_DT_CRE,
ID_SHIP_LINE_SRC,
ID_ORD_LINE_SRC,
ID_PACK_SRC,
ID_PRD_SRC,
TYP_SHIP,
SEQ,
DT_SHIP,
ID_PERS_SRC,
ID_CONTRACT,
NB_REAL_STK,
QTY,
CD_CANCEL,
D_CANCEL,
CD_BRAND,
CD_COUNTRY,
CD_BUSLINE,
DT_EXTRACT,
GETDATE()
FROM [dbo].SAS_SHIPPING_LINE WHERE SAS_STATUT='KO'
--delete from [dbo].SAS_SHIPPING_LINE where SAS_STATUT='KO'
 
-- archivage
INSERT INTO [dbo].SAS_SHIPPING_LINE_HISTO (
SAS_ID,SAS_STATUT,SAS_ERREUR,SAS_DT_CRE,
ID_SHIP_LINE_SRC,
ID_ORD_LINE_SRC,
ID_PACK_SRC,
ID_PRD_SRC,
TYP_SHIP,
SEQ,
DT_SHIP,
ID_PERS_SRC,
ID_CONTRACT,
NB_REAL_STK,
QTY,
CD_CANCEL,
D_CANCEL,
CD_BRAND,
CD_COUNTRY,
CD_BUSLINE,
DT_EXTRACT,
SAS_DT_HISTO
) SELECT SAS_ID,SAS_STATUT,SAS_ERREUR,SAS_DT_CRE,
ID_SHIP_LINE_SRC,
ID_ORD_LINE_SRC,
ID_PACK_SRC,
ID_PRD_SRC,
TYP_SHIP,
SEQ,
DT_SHIP,
ID_PERS_SRC,
ID_CONTRACT,
NB_REAL_STK,
QTY,
CD_CANCEL,
D_CANCEL,
CD_BRAND,
CD_COUNTRY,
CD_BUSLINE,
DT_EXTRACT,
GETDATE()
FROM [dbo].SAS_SHIPPING_LINE WHERE SAS_STATUT='OK'
--delete from [dbo].SAS_SHIPPING_LINE where SAS_STATUT='OK'
 
DROP TABLE #TMP_SHIPPING_LINE
end
------------------------------------------------------------------------------

[mikedavem]

Les sqlplans que vous nous avez donné ne correspondent pas à vos requêtes j'ai l'impression.

Par exemplelLes tables DWH_REF_COUNTRY et DWH_REF_BUSELINE sont utilisées alors que dans la requête postée il y a un commentaire sur ces tables ... ???

Au vu du nombre de lignes traités n'avez vous pas intérêt à transformer votre table temporaire en table de travail normale ?

++

[elsuket]

Bonjour,

Je viens de passer les plans de requête au peigne fin et je n'ai pas non plus trouvé la requête dont vous parlez.

En outre, vous avez un manque massif d'index sur votre base de données, et l'utilisation de table temporaires révèle un modèle de données mal fait.

On passera sur la présentation du code ...

@++

[Etienne5685]

D'accord pour les index

cette phase permet de charger un dwh on scan donc les tables du dwh pour vérifier les CI par exemple que la personne liée à l'enregistrement que l'on essaye d’insérer dans le DWH existe déjà dans le dwh la table temp permet de stocker la clé fonctionnelle de l'enregistrement ...

[Etienne5685]

pour les sqlplan il présente le traitement au complet (je pensai avoir isoler les deux requete ) pour les commentaires c'est des test que j'ai effectués entre deux post merci d'avoir pris le temps de regarder ces plans

ou me conseillez vous de mettre des index ? et de quel type clustered O/N nuique o/n?

[elsuket]

Étant donné que nous n'avons pas le plan de requête de la requête qui vous pose le plus gros problème, on ne peut pas vous aider.

Une petite mise au point sur les index.

Un index cluster doit être sur une colonne ou un jeu de colonnes dont chaque tuple de valeurs est unique.
Un index unique peut être cluster.
Un index de clé primaire peut ne pas être cluster.

@++

[Etienne5685]

Peut on forcer un plan d'exécution sous SQL serveur ?

[mikedavem]

Oui avec des HINT ou avec des guides de plan ... mais je pense que vous n'allez pas dans la bonne direction ... dans la plupart des cas forcer l'optimiseur est contre performant ... il faut avoir envisager toutes les solutions pour faire cela.

++

[Etienne5685]

encore moi

j'ai l'impression que sql server ne recréé pas son plan éxécution a chaque modification sur la table y t il une commande pour forcer ce calcul ?

[mikedavem]

De quel ordre est votre modification de table ? ALTER TABLE ???
Si tel est le cas une recompilation est automatiquement relancée par le moteur.

++