Utilisation des CTE et performances

Jinroh77 · 07/07/2011, 12h44

Bonjour,
Dans le cadre d'un reporting décisionnel, j'ai une requête relativement complexe à réaliser.

Le principe est de récupérer des données d'un certain périmètre depuis ma table de faits, avec des jointures sur les tables de dimensions, des regroupements pour des sommes etc.
Ce sont des données de vente que je stocke au niveau le plus fin.

Ensuite, une fois que j'ai ces données de mon périmètre je fais dessus différents différents select avec des niveaux de groupements différents que j'empile avec des UNION ALL.

Je n'ai pas la main sur le modèle de données et je ne peux pas non plus réaliser mes agrégations dans le reporting, les données présentant des particularités.

Bref, ma première idée était d'utiliser 1 requête unique avec des CTE, ensuite vu les temps de traitements, j'ai essayé avec une table temporaire, tout ce qu'il y a de plus simple.

v1 : Uniquement le select de base pour définir mon périmètre

v2 : CTE pour le périmètre puis CTE sur celle-ci pour les différents niveaux de vente

v3 : select de base INTO #tt et ensuite CTE sur cette table temporaire.

Voici les temps UC des différentes versions :

Citation:

--V1
(22987*ligne(s) affectée(s))

SQL Server \endash Temps d'exécution*:
, Temps UC = 3915*ms, temps écoulé = 16339*ms.

--V2
(34434*ligne(s) affectée(s))

SQL Server \endash Temps d'exécution*:
, Temps UC = 23510*ms, temps écoulé = 35975*ms.

--V3
SQL Server \endash Temps d'exécution*:
, Temps UC = 3354*ms, temps écoulé = 4224*ms.

(22987*ligne(s) affectée(s))

(34434*ligne(s) affectée(s))

SQL Server \endash Temps d'exécution*:
, Temps UC = 5277*ms, temps écoulé = 11961*ms.

On voit que la version avec la table temporaire semble beaucoup plus efficace que la version en CTE...
Plutôt qu'une table temporaire, je pourrais utiliser une vrai table mais ce qui m'étonne est le constat de base.

A part que ma requête peut largement être améliorée, que faut-il en penser ?
je pensais être plus efficace avec la CTE qu'une simple table temporaire ?
La mémoire de mon instance est-elle saturée ?
Il n'y a pourtant que 35 000 lignes environ.

Merci.

Pour info, la requête complète :

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
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
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
 
DECLARE @pIdMois				INT	= 201107
DECLARE @pIdRegionCommerciale	VARCHAR(MAX)	= '1,2,3'
DECLARE @pIdSecteur				VARCHAR(MAX)	= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'		
DECLARE @pIdGroupement			VARCHAR(MAX)	= '1,2'
 
 
DECLARE @AnneeCourante INT
SELECT DISTINCT @AnneeCourante = RTM_ANNEE FROM SBI_DWH.dbo.TR_TEMPS
WHERE RTM_ID_MOIS = @pIdMois
 
SET statistics time ON
;
WITH Vente AS (
SELECT
	RVE_CD_ENSEIGNE,
	--SBI_ETL.dbo.F_INITCAP(RVE_LB_ENSEIGNE) AS RVE_LB_ENSEIGNE,
	RVE_LB_ENSEIGNE,
 
	RVE_ID_REGION_COMMERCIALE,
	--SBI_ETL.dbo.F_INITCAP(RVE_LB_REGION_COMMERCIALE) AS RVE_LB_REGION_COMMERCIALE,
	RVE_LB_REGION_COMMERCIALE,
 
	RVE_ID_SECTEUR,
	--SBI_ETL.dbo.F_INITCAP(RVE_LB_SECTEUR) AS RVE_LB_SECTEUR,
	RVE_LB_SECTEUR,
 
	RVE_ID_GROUPEMENT_TERRAIN,
	--SBI_ETL.dbo.F_INITCAP(RVE_LB_GROUPEMENT_TERRAIN) AS RVE_LB_GROUPEMENT_TERRAIN,
	RVE_LB_GROUPEMENT_TERRAIN,
 
	RVE_ID_PDV,
	--SBI_ETL.dbo.F_INITCAP(RVE_LB_PT_VENTE)	AS RVE_LB_PT_VENTE,
	RVE_LB_PT_VENTE,
 
	ftVente.FSA_FK_ACTE_VENTE_GESTION AS FK_ACTE_VENTE_GESTION,
	avv.RTA_CD_ACTE,
	avv.RTA_LB_ACTE,
 
	ofr.ROF_CD_GAMME,
	ofr.ROF_LB_GAMME,	
	ctr.DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	art.RAR_LB_CATEGORIE_ARTICLE,	
	(CASE WHEN DCO_LB_NUM_APPEL_PORTAGE_IN IS NULL THEN 'Non' ELSE 'Oui' END) AS STATUT_PNM,
	SUM(
		CASE WHEN RTM_ID_MOIS = @pIdMois
			THEN ftVente.FSA_NB_ACTE
			ELSE 0 END
		)						AS NB_ACTE,
	SUM(ftVente.FSA_NB_ACTE)	AS NB_ACTE_CUMUL
 
FROM
	SBI_DWH.dbo.TF_SUIVI_ACTE_VENTE_GESTION ftVente
	JOIN SBI_DWH.dbo.TR_TEMPS tpsVente
		ON tpsVente.RTM_PK_TEMPS = ftVente.FSA_FK_DATE_VENTE
	JOIN SBI_DWH.dbo.TR_CANAL_VENTE cv
		ON cv.RVE_PK_CANAL_VENTE = ftVente.FSA_FK_CANAL_VENTE
	JOIN SBI_DWH.dbo.TR_ACTE_VENTE_GESTION avv
		ON avv.RTA_PK_ACTE_VENTE_GESTION = ftVente.FSA_FK_ACTE_VENTE_GESTION	
 
	LEFT OUTER JOIN SBI_DWH.dbo.TR_OFFRE ofr
		ON ofr.ROF_PK_OFFRE = ftVente.FSA_FK_OFFRE_VENTE
	LEFT OUTER JOIN SBI_DWH.dbo.TD_CONTRAT ctr
		ON ctr.DCO_PK_CONTRAT = ftVente.FSA_FK_CONTRAT
	LEFT OUTER JOIN SBI_DWH.dbo.TR_ARTICLE art
		ON art.RAR_PK_ARTICLE = ftVente.FSA_FK_ARTICLE
WHERE 
	tpsVente.RTM_ANNEE = @AnneeCourante
	AND tpsVente.RTM_ID_MOIS <= @pIdMois
	 AND RVE_LB_ENSEIGNE = 'LA POSTE'
	 AND avv.RTA_LB_TYPOLOGIE_ACTE = 'Vente'
GROUP BY
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
	RVE_ID_REGION_COMMERCIALE,
	RVE_LB_REGION_COMMERCIALE,
	RVE_ID_SECTEUR,
	RVE_LB_SECTEUR,
	RVE_ID_GROUPEMENT_TERRAIN,
	RVE_LB_GROUPEMENT_TERRAIN,
	RVE_ID_PDV,
	RVE_LB_PT_VENTE,
	ftVente.FSA_FK_ACTE_VENTE_GESTION,
	avv.RTA_CD_ACTE,
	avv.RTA_LB_ACTE,
	ofr.ROF_CD_GAMME,
	ofr.ROF_LB_GAMME,	
	ctr.DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	art.RAR_LB_CATEGORIE_ARTICLE,	
	(CASE WHEN DCO_LB_NUM_APPEL_PORTAGE_IN IS NULL THEN 'Non' ELSE 'Oui' END)
),
 
/*****************************************
** Calcul des ventes pour chaque niveau **
*****************************************/
VenteNiveau AS (
				/* Niveau Bdp */
SELECT
	4 AS Ordre,
	'BDP' AS Niveau,
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
	RVE_ID_REGION_COMMERCIALE,
	RVE_LB_REGION_COMMERCIALE,
	RVE_ID_SECTEUR,
	RVE_LB_SECTEUR,
	RVE_ID_GROUPEMENT_TERRAIN,
	RVE_LB_GROUPEMENT_TERRAIN,
	RVE_ID_PDV,
	RVE_LB_PT_VENTE,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	SUM(NB_ACTE)		AS NB_ACTE,
	SUM(NB_ACTE_CUMUL)	AS NB_ACTE_CUMUL
FROM Vente
--where 
--	RVE_ID_GROUPEMENT_TERRAIN   in 
--		(select [value] from SBI_ETL.dbo.fn_Split(@pIdGroupement,','))
GROUP BY
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
	RVE_ID_REGION_COMMERCIALE,
	RVE_LB_REGION_COMMERCIALE,
	RVE_ID_SECTEUR,
	RVE_LB_SECTEUR,
	RVE_ID_GROUPEMENT_TERRAIN,
	RVE_LB_GROUPEMENT_TERRAIN,
	RVE_ID_PDV,
	RVE_LB_PT_VENTE,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM
 
UNION ALL
 
				/* Niveau Groupement */
SELECT
	3 AS Ordre,
	'GP' AS Niveau,
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
	RVE_ID_REGION_COMMERCIALE,
	RVE_LB_REGION_COMMERCIALE,
	RVE_ID_SECTEUR,
	RVE_LB_SECTEUR,
	RVE_ID_GROUPEMENT_TERRAIN,
	RVE_LB_GROUPEMENT_TERRAIN,
	NULL,
	NULL,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	SUM(NB_ACTE)	AS NB_ACTE,
	SUM(NB_ACTE_CUMUL)	AS NB_ACTE_CUMUL
FROM Vente
--where 
--	RVE_ID_GROUPEMENT_TERRAIN   in 
--		(select [value] from SBI_ETL.dbo.fn_Split(@pIdGroupement,','))
GROUP BY
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
	RVE_ID_REGION_COMMERCIALE,
	RVE_LB_REGION_COMMERCIALE,
	RVE_ID_SECTEUR,
	RVE_LB_SECTEUR,
	RVE_ID_GROUPEMENT_TERRAIN,
	RVE_LB_GROUPEMENT_TERRAIN,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM
 
UNION ALL
 
				/* Niveau Secteur */
SELECT
	2 AS Ordre,
	'SCT' AS Niveau,
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
	RVE_ID_REGION_COMMERCIALE,
	RVE_LB_REGION_COMMERCIALE,
	RVE_ID_SECTEUR,
	RVE_LB_SECTEUR,
	NULL,
	NULL,
	NULL,
	NULL,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	SUM(NB_ACTE)	AS NB_ACTE,
	SUM(NB_ACTE_CUMUL)	AS NB_ACTE_CUMUL
FROM Vente
WHERE 
	RVE_ID_SECTEUR   IN 
		(SELECT [value] FROM SBI_ETL.dbo.fn_Split(@pIdSecteur,','))
GROUP BY
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
	RVE_ID_REGION_COMMERCIALE,
	RVE_LB_REGION_COMMERCIALE,
	RVE_ID_SECTEUR,
	RVE_LB_SECTEUR,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM
 
UNION ALL
 
				/* Niveau Region commerciale */
SELECT
	1 AS Ordre,
	'RG' AS Niveau,
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
	RVE_ID_REGION_COMMERCIALE,
	RVE_LB_REGION_COMMERCIALE,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	SUM(NB_ACTE)	AS NB_ACTE,
	SUM(NB_ACTE_CUMUL)	AS NB_ACTE_CUMUL
FROM Vente
WHERE 
	RVE_ID_REGION_COMMERCIALE   IN 
		(SELECT [value] FROM SBI_ETL.dbo.fn_Split(@pIdRegionCommerciale,','))
GROUP BY
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
	RVE_ID_REGION_COMMERCIALE,
	RVE_LB_REGION_COMMERCIALE,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM
 
UNION ALL
 
				/* Niveau Region commerciale */
SELECT
	0 AS Ordre,
	'ENS' AS Niveau,
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	SUM(NB_ACTE)	AS NB_ACTE,
	SUM(NB_ACTE_CUMUL)	AS NB_ACTE_CUMUL
FROM Vente
WHERE 
	RVE_LB_ENSEIGNE = 'LA POSTE'
GROUP BY
	RVE_CD_ENSEIGNE,
	RVE_LB_ENSEIGNE,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM
),
/*****************************************************
** Création de hiérarchie récurssive pour l'édition **
******************************************************/
VenteRapport AS (
SELECT 
	Ordre,
	Niveau,
	'GP' + cast(RVE_ID_GROUPEMENT_TERRAIN AS varchar)	AS IdPere,
	Niveau + cast(RVE_ID_PDV AS varchar)			AS IdPdVente,
	RVE_LB_PT_VENTE		AS LBPdVente,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	NB_ACTE,
	NB_ACTE_CUMUL
FROM VenteNiveau
WHERE Niveau = 'BDP'
UNION ALL
SELECT 
	Ordre,
	Niveau,
	'SCT' + cast(RVE_ID_SECTEUR AS varchar)	AS IdPere,
	Niveau + cast(RVE_ID_GROUPEMENT_TERRAIN AS varchar)			AS IdPdVente,
	RVE_LB_GROUPEMENT_TERRAIN		AS LBPdVente,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	NB_ACTE,
	NB_ACTE_CUMUL 
FROM VenteNiveau
WHERE Niveau = 'GP'
UNION ALL
SELECT 
	Ordre,
	Niveau,
	'RG' + cast(RVE_ID_REGION_COMMERCIALE AS varchar)	AS IdPere,
	Niveau + cast(RVE_ID_SECTEUR AS varchar)			AS IdPdVente,
	RVE_LB_SECTEUR		AS LBPdVente,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	NB_ACTE,
	NB_ACTE_CUMUL
FROM VenteNiveau
WHERE Niveau = 'SCT'
UNION ALL
SELECT 
	Ordre,
	Niveau,
	'ENS' + cast(RVE_CD_ENSEIGNE AS varchar)	AS IdPere,
	Niveau + cast(RVE_ID_REGION_COMMERCIALE	 AS varchar)		AS IdPdVente,
	RVE_LB_REGION_COMMERCIALE		AS LBPdVente,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	NB_ACTE,
	NB_ACTE_CUMUL 
FROM VenteNiveau
WHERE Niveau = 'RG'
 
UNION ALL
SELECT 
	Ordre,
	Niveau,
	'ENS' + cast(RVE_CD_ENSEIGNE AS varchar)		AS IdPere,
	Niveau + cast(RVE_CD_ENSEIGNE AS varchar)		AS IdPdVente,
	RVE_LB_ENSEIGNE		AS LBPdVente,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	NB_ACTE,
	NB_ACTE_CUMUL 
FROM VenteNiveau
WHERE Niveau = 'ENS'
)
 
SELECT
	Ordre,
	Niveau,
	IdPere,
	IdPdVente,
	LBPdVente,
 
	FK_ACTE_VENTE_GESTION,
	RTA_CD_ACTE,
	RTA_LB_ACTE,
 
	ROF_CD_GAMME,
	ROF_LB_GAMME,	
	DCO_NB_MOIS_ENGAGEMENT_INITIAL,	
	RAR_LB_CATEGORIE_ARTICLE,	
	STATUT_PNM,
	NB_ACTE,
	NB_ACTE_CUMUL  
FROM VenteRapport
 
SET statistics time OFF

Waldar · 07/07/2011, 13h48

Vous pouvez éviter tous les union all avec le ROLLUP.
Un exemple à adapter :

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
; WITH MaTable (Pays, Region, Ville, NbVente) AS
(
SELECT 'FRANCE', 'R1', 'VA', 1 union ALL
SELECT 'FRANCE', 'R1', 'VB', 1 union ALL
SELECT 'FRANCE', 'R2', 'VC', 1 union ALL
SELECT 'CANADA', 'RZ', 'V1', 1 union ALL
SELECT 'CANADA', 'RY', 'V2', 1 union ALL
SELECT 'CANADA', 'RY', 'V3', 1
)
  SELECT case grouping(Pays)   when 1 then 'TOTAL MONDE'     else Pays   end AS Pays
       , case grouping(Region) when 1 then 'TOTAL ' + Pays   else Region end AS Region
       , case grouping(Ville)  when 1 then 'TOTAL ' + Region else Ville  end AS Ville
       , sum(NbVente)                                                        AS NbVente
    FROM MaTable
GROUP BY Pays, Region, Ville WITH rollup
 
 
Pays        Region       Ville    NbVente
----------- ------------ -------- -----------
CANADA      RY           V2       1
CANADA      RY           V3       1
CANADA      RY           TOTAL RY 2
CANADA      RZ           V1       1
CANADA      RZ           TOTAL RZ 1
CANADA      TOTAL CANADA NULL     3
FRANCE      R1           VA       1
FRANCE      R1           VB       1
FRANCE      R1           TOTAL R1 2
FRANCE      R2           VC       1
FRANCE      R2           TOTAL R2 1
FRANCE      TOTAL FRANCE NULL     3
TOTAL MONDE NULL         NULL     6

La syntaxe du ROLLUP est affinée à partir de SQL-Server 2008.

elsuket · 07/07/2011, 13h54

Bonjour,

Il n'y a pas de récursivité dans vos expressions de table commune puisqu'il n'y a jamais de jointure entre une ancre et une partie récursive de celles-ci.

Les CTE récursives peuvent produire des plans aux cardinalités foireuses si la jointure a une cardinalité bien supérieure à 1.

@++

Jinroh77 · 07/07/2011, 14h02

Merci pour le ROLLUP, ce sera effectivement une bonne chose.

Pour la récursivité, je n’utilise effectivement pas. Les CTE ne sont "intéressantes" qu'en cas de récurcivité ?

Waldar · 07/07/2011, 14h06

Je les utilise aussi en lieue et place de vue imbriquée, je trouve la compréhension des requêtes plus aisées ainsi.

Jinroh77 · 07/07/2011, 14h10

Je préférais aussi par une requête unique, compréhensible, travaillant uniquement en mémoire etc. plutôt que de passer par exemple par une table temporaire.
Cette différence de performance n'est-elle pas bizarre ?

SQLpro · 07/07/2011, 15h10

Sans la définition de vos tables et des index il est impossible de vous aider plafonnement.

Une solution plus efficace est de ne véhiculer les détails que dans la requête finale après agrégation.

Exemple :

Code :

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

1
2
3
4
 
SELECT NOM, PRENOM, DATE_NAISSANCE, COUNT(*) AS NOMBRE
FROM   MaTable
GROUP  BY NOM, PRENOM, DATE_NAISSANCE

est souvent plus couteux si la requêtes est complexe que :

Code :

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

1
2
3
4
5
6
7
8
WITH T AS 
(SELECT CLEF, COUNT(*) AS NOMBRE
 FROM   MaTable
 GROUP  BY NOM, PRENOM, DATE_NAISSANCE)
SELECT T1.NOM, T1.PRENOM, T1.DATE_NAISSANCE, NOMBRE
FROM   MaTable AS T1
       INNER JOIN T
             ON t1.CLEF = T.CLEF

A +

Sergejack · 07/07/2011, 15h38

Une CTE ne provoque pas en lui-même de query.
Càd que si vous avez CTE1, CTE2, QUERY : CTE1 ne sera ni "fait" en premier ni indépendamment de QUERY, CTE2 ne ser ni "fait" en second ni indépendamment de QUERY, et d'ailleurs si CTE1 et CTE2 ne sont pas employés dans QUERY, ces dernières n'auront aucun effet et seront simplement "ignorées".

Voyez une CTE comme du SQL que vous choisissez d'écrire une fois à un endroit pour l'insérer ailleurs une ou plusieurs fois (mais ne voyez pas que ça, la récursivité est une possibilité importante offerte par les CTE).

Citation:

Envoyé par SQLpro

Une solution plus efficace est de ne véhiculer les détails que dans la requête finale après agrégation.

Exemple :

Code :

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

1
2
3
4
 
SELECT NOM, PRENOM, DATE_NAISSANCE, COUNT(*) AS NOMBRE
FROM   MaTable
GROUP  BY NOM, PRENOM, DATE_NAISSANCE

est souvent plus couteux si la requêtes est complexe que :

Code :

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

1
2
3
4
5
6
7
8
WITH T AS 
(SELECT CLEF, COUNT(*) AS NOMBRE
 FROM   MaTable
 GROUP  BY NOM, PRENOM, DATE_NAISSANCE)
SELECT T1.NOM, T1.PRENOM, T1.DATE_NAISSANCE, NOMBRE
FROM   MaTable AS T1
       INNER JOIN T
             ON t1.CLEF = T.CLEF

A +

Je ne vous suis pas, d'autant plus que je suis convaincu que ce second query ne fonctionne pas (CLEF n'est pas agrégé).

Jinroh77 · 07/07/2011, 15h47

Je sais que sans le modèle complet c'est plus compliqué à évaluer, mais plutôt que de me fournir une requête toute belle, c'était déjà pour les idées et concepts de ces objets.

Merci pour la précisions sur les CTE, je pensais que le résultat de la requête était d'abord mis en mémoire, puis utilisé x-fois dans mes différentes requêtes suivantes.
c'est vrai que lorsque je regarde le plan d’exécution, les tables de ma requête de base sont visible un certains nombre de fois.

SQLpro · 07/07/2011, 16h15

Citation:

Envoyé par Sergejack

Je ne vous suis pas, d'autant plus que je suis convaincu que ce second query ne fonctionne pas (CLEF n'est pas agrégé).

Il suffit de rajouter un DISTINCT dans le SELECT final.

A +

Sergejack · 07/07/2011, 16h31

Citation:

Envoyé par SQLpro

Il suffit de rajouter un DISTINCT dans le SELECT final.

A +

Vous auriez un exemple complet ?

Jinroh77 · 07/07/2011, 17h13

Mis à part passer par une table temporaire, et ne pouvant créer une table d'agrégats, de quels autres moyens est-ce que je dispose pour éviter les CTE qui, non récursives mais simplement utilisées plusieurs fois, ne sont pas vraiment l'idéal ?

Waldar · 07/07/2011, 17h18

Cf. ma première réponse, vous n'avez besoin ici ni de CTE, ni de table temporaire.
Il ne faut que réécrire la requête avec l'utilisation du ROLLUP.

Jinroh77 · 07/07/2011, 17h31

Hummm oui, c'est vrai que ça marche dans ce cas... mais prenons un autre exemple

Cette fois je récupère les ventes de tous mes points de ventes, par gamme.
Ensuite je calcule dessus un top et un flop, mais par regroupement de ces gammes.
je dois alors restituer le top/flop par regroupement mais conserver l'information du détail de la gamme.
De la même manière j'avais une CTE pour récupérer le périmètre, 1 pour calculer les top/flop et un select de jointure pour la restitution.

Sur ce périmètre plus réduit, je passe tout de même de

Citation:

(10717*ligne(s) affectée(s))

SQL Server \endash Temps d'exécution*:
, Temps UC = 4617*ms, temps écoulé = 7707*ms.

à ça, avec 1 table #temp pour remplacer la première CTE :

Citation:

SQL Server \endash Temps d'exécution*:
, Temps UC = 1794*ms, temps écoulé = 2140*ms.

(10717*ligne(s) affectée(s))

(10717*ligne(s) affectée(s))

SQL Server \endash Temps d'exécution*:
, Temps UC = 109*ms, temps écoulé = 3277*ms.

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
 
;
WITH Vente AS (
SELECT 
	cv.RVE_ID_PDV,
	--SBI_ETL.dbo.F_INITCAP(cv.RVE_LB_PT_VENTE) AS RVE_LB_PT_VENTE,
	RVE_LB_PT_VENTE,
 
	avv.RTA_CD_ACTE,
	avv.RTA_LB_ACTE,
	CASE WHEN avv.RTA_CD_ACTE = 'PP' THEN COALESCE(art.RAR_LB_CATEGORIE_ARTICLE, 'Inconnu') ELSE ofr.ROF_LB_GAMME END AS ROF_LB_GAMME,
	SUM(ftVente.FSA_NB_ACTE) AS NB_ACTE
FROM
	SBI_DWH.dbo.TF_SUIVI_ACTE_VENTE_GESTION ftVente
	JOIN SBI_DWH.dbo.TR_TEMPS tpsVente
		ON tpsVente.RTM_PK_TEMPS = ftVente.FSA_FK_DATE_VENTE
	JOIN SBI_DWH.dbo.TR_CANAL_VENTE cv
		ON cv.RVE_PK_CANAL_VENTE = ftVente.FSA_FK_CANAL_VENTE
	JOIN SBI_DWH.dbo.TR_ACTE_VENTE_GESTION avv
		ON avv.RTA_PK_ACTE_VENTE_GESTION = ftVente.FSA_FK_ACTE_VENTE_GESTION
	LEFT OUTER JOIN SBI_DWH.dbo.TR_OFFRE ofr
		ON ofr.ROF_PK_OFFRE = ftVente.FSA_FK_OFFRE_VENTE
	LEFT OUTER JOIN SBI_DWH.dbo.TR_ARTICLE art
		ON ftVente.FSA_FK_ARTICLE = art.RAR_PK_ARTICLE
WHERE 
	tpsVente.RTM_ANNEE = 2011
	AND tpsVente.RTM_ID_MOIS IN 
		(SELECT [value] FROM SBI_ETL.dbo.fn_Split('201104, 201105, 201106, 201107',','))
 
	AND RVE_LB_ENSEIGNE = 'LA POSTE'
	AND	avv.RTA_LB_TYPOLOGIE_ACTE = 'Vente'
	AND avv.RTA_CD_ACTE IN ('ABO', 'FBR', 'PP', 'REC', 'ASSU')
 
	AND RVE_ID_REGION_COMMERCIALE IN 
		(SELECT [value] FROM SBI_ETL.dbo.fn_Split('1,2,3',','))
	AND RVE_ID_SECTEUR IN 
		(SELECT [value] FROM SBI_ETL.dbo.fn_Split('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15',','))
	--AND RVE_ID_GROUPEMENT_TERRAIN in
	--	(select [value] from SBI_ETL.dbo.fn_Split(@pIdGroupement,','))
GROUP BY
	cv.RVE_ID_PDV,
	cv.RVE_LB_PT_VENTE,
	avv.RTA_CD_ACTE,
	avv.RTA_LB_ACTE,
	CASE WHEN avv.RTA_CD_ACTE = 'PP' THEN COALESCE(art.RAR_LB_CATEGORIE_ARTICLE, 'Inconnu') ELSE ofr.ROF_LB_GAMME END
),
Rang AS (
SELECT
	RVE_ID_PDV,
	RVE_LB_PT_VENTE,
	CASE WHEN RTA_CD_ACTE IN ('ABO', 'FBR','PP')
		THEN 'MIX'
		ELSE RTA_CD_ACTE 
	END AS GP_ACTE,
	CASE WHEN RTA_CD_ACTE IN ('ABO', 'FBR','PP')
		THEN 'Mix (Abonnement, FBR, PP)'
		ELSE RTA_LB_ACTE 
	END AS LB_ACTE,
	SUM(NB_ACTE) AS Total,
	RANK ()	OVER (PARTITION BY 
								CASE WHEN RTA_CD_ACTE IN ('ABO', 'FBR','PP')
									THEN 'MIX'
									ELSE RTA_CD_ACTE 
								END
						  ORDER BY SUM(NB_ACTE) DESC) AS RangTop,
	RANK ()	OVER (PARTITION BY 
								CASE WHEN RTA_CD_ACTE IN ('ABO', 'FBR','PP')
									THEN 'MIX'
									ELSE RTA_CD_ACTE 
								END
						  ORDER BY SUM(NB_ACTE) ASC) AS RangFlop
FROM
	Vente
GROUP BY
	RVE_ID_PDV,
	RVE_LB_PT_VENTE,
	CASE WHEN RTA_CD_ACTE IN ('ABO', 'FBR','PP')
		THEN 'MIX'
		ELSE RTA_CD_ACTE END,
	CASE WHEN RTA_CD_ACTE IN ('ABO', 'FBR','PP')
		THEN 'Mix (Abonnement, FBR, PP)'
		ELSE RTA_LB_ACTE END
)
 
SELECT 
	CASE WHEN GP_ACTE = 'MIX' THEN 'T1' ELSE
	CASE WHEN GP_ACTE = 'ASSU' THEN 'T3' ELSE
	CASE WHEN GP_ACTE = 'REC' THEN 'T2' END END END AS NumeroTableau,
 
	CASE WHEN GP_ACTE = 'MIX' THEN 'Mix Forfaits' ELSE
	CASE WHEN GP_ACTE = 'ASSU' THEN 'Assurances' ELSE
	CASE WHEN GP_ACTE = 'REC' THEN 'Recharges' END END END AS NomTableau,
 
	Vente.RVE_ID_PDV,
	Vente.RVE_LB_PT_VENTE,
	rang.GP_ACTE,
	rang.LB_ACTE,
	Vente.RTA_CD_ACTE,
	Vente.RTA_LB_ACTE,
	Vente.ROF_LB_GAMME,
	Vente.NB_ACTE,
	rang.Total AS TotalBdpActe,
	rang.RangTop AS RangTopBdpActe,
	rang.RangFlop AS RangFlopBdpActe
FROM
	Vente
	JOIN Rang
		ON Rang.RVE_ID_PDV = vente.RVE_ID_PDV 
		AND Rang.GP_ACTE = 	
				CASE WHEN vente.RTA_CD_ACTE IN ('ABO', 'FBR','PP')
				THEN 'MIX'
				ELSE vente.RTA_CD_ACTE END

Jinroh77 · 08/07/2011, 15h10

Je vois que cette dernière digression vers une requête n'a pas beaucoup inspirée les foules...

Je reviens donc sur la première et l'idée du ROLLUP. En fait je me souvient pourquoi je ne peux l'utiliser.
Le problème est que, malgré les niveaux hiérarchiques de la requête (groupement > secteur > région etc.), chacun des niveaux est filtré.
L'utilisateur filtre chacun des niveaux ; mais il peut très bien vouloir restituer 1 seul groupement mais par contre il souhaite toujours voir le total du secteur (tous groupements confondus) auquel appartient ce groupement.

L'idée serait peut-être alors de restituer chacun des niveaux au complet, de manière plus simple et sûrement plus rapide, puis de filtrer ensuite l'affichage dans l'outil de reporting ?

Waldar · 08/07/2011, 15h13

Oui ce serait une idée, si vous écrivez juste le ROLLUP tous les sous-totaux sont effectués, après on peut effectivement en gérer l'affichage ou non.

Pour votre constat, difficile de tirer de grandes conclusions, on n'a ni vos données ni votre environnement... Vous dire pourquoi une table temporaire fonctionne mieux ici qu'une CTE, j'en suis bien incapable.

07/07/2011, 13h54	#3
elsuket Modérateur Nicolas Souquet Administrateur de base de données Inscription : janvier 2005 Messages : 4 669 Détails du profil Informations personnelles : Nom : Nicolas Souquet Âge : 30 Localisation : Thaïlande Informations professionnelles : Activité : Administrateur de base de données Secteur : High Tech - Éditeur de logiciels Informations forums : Inscription : janvier 2005 Messages : 4 669 Points : 8 729 Points : 8 729	Bonjour, Il n'y a pas de récursivité dans vos expressions de table commune puisqu'il n'y a jamais de jointure entre une ancre et une partie récursive de celles-ci. Les CTE récursives peuvent produire des plans aux cardinalités foireuses si la jointure a une cardinalité bien supérieure à 1. @++ __________________ En bases de données relationnelles SQL, il n'y a ni tableaux, ni enregistrements, ni champs: il y a des tables, des lignes et des colonnes. Blog \| Profil\| Consulter ou télécharger les fichiers d'aide de SQL Server, des versions 2000 à 2012
	00

07/07/2011, 14h06	#5
Waldar Modérateur Fabien Ingénieur d'études en décisionnel Inscription : septembre 2008 Messages : 5 684 Détails du profil Informations personnelles : Nom : Fabien Âge : 34 Localisation : France, Yvelines (Île de France) Informations professionnelles : Activité : Ingénieur d'études en décisionnel Secteur : Arts - Culture Informations forums : Inscription : septembre 2008 Messages : 5 684 Points : 10 433 Points : 10 433	Je les utilise aussi en lieue et place de vue imbriquée, je trouve la compréhension des requêtes plus aisées ainsi. __________________ Email : http://scr.im/waldar
	00

07/07/2011, 17h18	#13
Waldar Modérateur Fabien Ingénieur d'études en décisionnel Inscription : septembre 2008 Messages : 5 684 Détails du profil Informations personnelles : Nom : Fabien Âge : 34 Localisation : France, Yvelines (Île de France) Informations professionnelles : Activité : Ingénieur d'études en décisionnel Secteur : Arts - Culture Informations forums : Inscription : septembre 2008 Messages : 5 684 Points : 10 433 Points : 10 433	Cf. ma première réponse, vous n'avez besoin ici ni de CTE, ni de table temporaire. Il ne faut que réécrire la requête avec l'utilisation du ROLLUP. __________________ Email : http://scr.im/waldar
	00

08/07/2011, 15h10	#15
Jinroh77 Modérateur Alexandre Chemla Consultant en Business Intelligence Inscription : février 2006 Messages : 1 773 Détails du profil Informations personnelles : Nom : Alexandre Chemla Âge : 28 Localisation : France, Seine et Marne (Île de France) Informations professionnelles : Activité : Consultant en Business Intelligence Informations forums : Inscription : février 2006 Messages : 1 773 Points : 1 837 Points : 1 837	Je vois que cette dernière digression vers une requête n'a pas beaucoup inspirée les foules... Je reviens donc sur la première et l'idée du ROLLUP. En fait je me souvient pourquoi je ne peux l'utiliser. Le problème est que, malgré les niveaux hiérarchiques de la requête (groupement > secteur > région etc.), chacun des niveaux est filtré. L'utilisateur filtre chacun des niveaux ; mais il peut très bien vouloir restituer 1 seul groupement mais par contre il souhaite toujours voir le total du secteur (tous groupements confondus) auquel appartient ce groupement. L'idée serait peut-être alors de restituer chacun des niveaux au complet, de manière plus simple et sûrement plus rapide, puis de filtrer ensuite l'affichage dans l'outil de reporting ? __________________ Alexandre Chemla - Consultant MS BI chez Masao
	00

08/07/2011, 15h13	#16
Waldar Modérateur Fabien Ingénieur d'études en décisionnel Inscription : septembre 2008 Messages : 5 684 Détails du profil Informations personnelles : Nom : Fabien Âge : 34 Localisation : France, Yvelines (Île de France) Informations professionnelles : Activité : Ingénieur d'études en décisionnel Secteur : Arts - Culture Informations forums : Inscription : septembre 2008 Messages : 5 684 Points : 10 433 Points : 10 433	Oui ce serait une idée, si vous écrivez juste le ROLLUP tous les sous-totaux sont effectués, après on peut effectivement en gérer l'affichage ou non. Pour votre constat, difficile de tirer de grandes conclusions, on n'a ni vos données ni votre environnement... Vous dire pourquoi une table temporaire fonctionne mieux ici qu'une CTE, j'en suis bien incapable. __________________ Email : http://scr.im/waldar
	00

07/07/2011, 14h02	#4
Jinroh77 Modérateur Alexandre Chemla Consultant en Business Intelligence Inscription : février 2006 Messages : 1 773 Détails du profil Informations personnelles : Nom : Alexandre Chemla Âge : 28 Localisation : France, Seine et Marne (Île de France) Informations professionnelles : Activité : Consultant en Business Intelligence Informations forums : Inscription : février 2006 Messages : 1 773 Points : 1 837 Points : 1 837	Merci pour le ROLLUP, ce sera effectivement une bonne chose. Pour la récursivité, je n’utilise effectivement pas. Les CTE ne sont "intéressantes" qu'en cas de récurcivité ?
	00

07/07/2011, 14h10	#6
Jinroh77 Modérateur Alexandre Chemla Consultant en Business Intelligence Inscription : février 2006 Messages : 1 773 Détails du profil Informations personnelles : Nom : Alexandre Chemla Âge : 28 Localisation : France, Seine et Marne (Île de France) Informations professionnelles : Activité : Consultant en Business Intelligence Informations forums : Inscription : février 2006 Messages : 1 773 Points : 1 837 Points : 1 837	Je préférais aussi par une requête unique, compréhensible, travaillant uniquement en mémoire etc. plutôt que de passer par exemple par une table temporaire. Cette différence de performance n'est-elle pas bizarre ?
	00

07/07/2011, 15h47	#9
Jinroh77 Modérateur Alexandre Chemla Consultant en Business Intelligence Inscription : février 2006 Messages : 1 773 Détails du profil Informations personnelles : Nom : Alexandre Chemla Âge : 28 Localisation : France, Seine et Marne (Île de France) Informations professionnelles : Activité : Consultant en Business Intelligence Informations forums : Inscription : février 2006 Messages : 1 773 Points : 1 837 Points : 1 837	Je sais que sans le modèle complet c'est plus compliqué à évaluer, mais plutôt que de me fournir une requête toute belle, c'était déjà pour les idées et concepts de ces objets. Merci pour la précisions sur les CTE, je pensais que le résultat de la requête était d'abord mis en mémoire, puis utilisé x-fois dans mes différentes requêtes suivantes. c'est vrai que lorsque je regarde le plan d’exécution, les tables de ma requête de base sont visible un certains nombre de fois.
	00

07/07/2011, 17h13	#12
Jinroh77 Modérateur Alexandre Chemla Consultant en Business Intelligence Inscription : février 2006 Messages : 1 773 Détails du profil Informations personnelles : Nom : Alexandre Chemla Âge : 28 Localisation : France, Seine et Marne (Île de France) Informations professionnelles : Activité : Consultant en Business Intelligence Informations forums : Inscription : février 2006 Messages : 1 773 Points : 1 837 Points : 1 837	Mis à part passer par une table temporaire, et ne pouvant créer une table d'agrégats, de quels autres moyens est-ce que je dispose pour éviter les CTE qui, non récursives mais simplement utilisées plusieurs fois, ne sont pas vraiment l'idéal ?
	00

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