lenteur d'une requète CTE

**PickEpique** · 26/10/2009, 16h17

Je poste ici à cause de la spécificité des requêtes CTE SQL server 2005.
Donc sur SQL server 2005 et Del PowerEdge 610, la requète CTE suivante devient de plus en plus longue au fur et à mesure de l'ajout de données; si je regarde le plan d'execution joint dans le ZIP j'ai effectivement un index scan sur la clef primaire cela semble être du évidemment à la construction à base de cte mais je ne comprend pas pourquoi ?

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
 
WITH CTE1
	AS 
	(
		SELECT 
			T1.DEV_ID,
			T1.DEV_PA1,
			T1.DEV_PA2,
			T1.DEV_PVHF,
			T1.DEV_PV1,
			T1.DEV_PVF,
			T1.DEV_COEFF_DIFF,
			T1.DEV_MT_FRAIS,
			T1.DEV_TYPE_RAB,	
			T1.DEV_MT_RABAIS,
			T1.DEV_PCT_RABAIS,
			T1.DEV_MT_MO,
			T1.DEV_TAUX_TVA
		FROM dbo.T_DEVIS_DEV AS T1
	),
	CTE2
	AS
    (
		SELECT 
			DEV_ID,
			DEV_PA1,
			DEV_PA2,
			DEV_PVHF,
			DEV_PV1,
			DEV_PVF,
			DEV_COEFF_DIFF,
			DEV_MT_FRAIS,
			DEV_TYPE_RAB,	
			DEV_MT_RABAIS,
			DEV_PCT_RABAIS,
			DEV_MT_MO,
			DEV_PA2 - DEV_MT_MO AS DEV_MT_MAT,
			DEV_TAUX_TVA,
			dbo.Ufn_Divise_Prot0(DEV_PA2 , DEV_PA1, 1) AS DEV_COEFF_ECO,
			CASE ISNULL(DEV_MT_FRAIS,0)
				WHEN  0 THEN 1.00
				ELSE 1 + dbo.Ufn_Divise_Prot0(DEV_MT_FRAIS, DEV_PVHF, 1)
			END AS DEV_COEFF_FRAIS,
			CASE DEV_TYPE_RAB
				WHEN 0 THEN DEV_PVF - ISNULL(DEV_MT_RABAIS,0)
				WHEN 1 THEN DEV_PVF * (1 - (ISNULL(DEV_PCT_RABAIS,0) / 100))
			END DEV_PVFR,
			DEV_PVF - DEV_PV1 AS DEV_MT_AJUST
		FROM CTE1
	),
	CTE3
	AS
    (
		SELECT 
			DEV_ID,
			DEV_PA1,
			DEV_PA2,
			DEV_PVHF,
			DEV_PV1,
			DEV_PVF,
			DEV_COEFF_DIFF,
			DEV_MT_FRAIS,
			DEV_TYPE_RAB,	
			DEV_MT_RABAIS,
			DEV_PCT_RABAIS,
			DEV_MT_MO,
			DEV_TAUX_TVA,
			DEV_COEFF_ECO, 						 
			DEV_COEFF_FRAIS,
			DEV_PVFR,
			DEV_COEFF_DIFF * DEV_COEFF_FRAIS AS DEV_COEFF_GEN,
			DEV_PVFR * ( 1 + (DEV_TAUX_TVA / 100)) AS DEV_PVFR_TTC,
			DEV_MT_AJUST,
			DEV_PVFR - DEV_PA2 AS DEV_MB,
			dbo.Ufn_Divise_Prot0(DEV_PVFR ,DEV_PA2, 1) AS DEV_COEFF,
			DEV_MT_MAT
		FROM CTE2
	)
    SELECT
		T1.DEV_ID,
		T1.DET_ID,
		T1.DST_ID,
		T1.DEV_ISCALC,
		T1.DEV_ID_SRC,
		T1.AFF_ID,
		T1.TIE_ID AS TIE_ID_DEV,
		T1.DEV_REF_PRI,
		T1.DEV_REF_SEC,
		T1.DEV_LIBELLE,
		T1.DEV_DATE_PUB,
		T1.DEV_REDACTEUR,
		T1.DEV_TEXT_PD,
		T1.DEV_TEXT_PF,
		T1.DEV_MEMO_INTERNE,
		T1.DEV_MEMO_HISTO,
		T1.DEV_PVST,
		T1.DEV_NB_HRS_MO,
		T1.DEV_USECRE,
		T1.DEV_DACRE,
		T1.DEV_USEMAJ,
		T1.DEV_DAMAJ,
		T1.DEV_MT_REAL,
		T1.DEV_MEMO_SITU,
 
		T1B.DEV_REF_PRI AS DEV_REF_PRI_SRC, 
 
		T2.DEV_PA1,
		T2.DEV_PA2,
		T2.DEV_PVHF,
		T2.DEV_MT_FRAIS, 
		T2.DEV_COEFF_ECO,
		T2.DEV_COEFF_FRAIS,
		T1.DEV_COEFF_DIFF,
		T2.DEV_COEFF_GEN,
		T2.DEV_PV1, 
		T2.DEV_PVF,
		T2.DEV_MT_AJUST,
        T2.DEV_TYPE_RAB,
		T2.DEV_MT_RABAIS,
		T2.DEV_PCT_RABAIS, 
		T2.DEV_PVFR,
		T2.DEV_MB,
		T2.DEV_COEFF,
		T2.DEV_MT_MO,
		dbo.Ufn_Divise_Prot0(T2.DEV_MT_MO,T2.DEV_PA2,100) AS DEV_RATIO_MO, 
		T2.DEV_TAUX_TVA,
		T2.DEV_PVFR_TTC - T2.DEV_PVFR AS DEV_MT_TVA,
		T2.DEV_PVFR_TTC,
		T2.DEV_MT_MAT,
 
		T3.AFF_REF, 
		T3.AFF_LIBELLE,
 
		T4.TIE_ID AS TIE_ID_AFF,
		T4.TIE_REF AS TIE_REF_AFF,
		T4.TIE_NOM AS TIE_NOM_AFF,
 
        T5.DET_LIBELLE,
 
		T6.DST_LIBELLE,
 
		T7.TIE_REF AS TIE_REF_DEV,
		T7.TIE_NOM AS TIE_NOM_DEV
 
     FROM
		dbo.T_DEVIS_DEV AS T1 
			INNER JOIN CTE3 AS T2 ON T1.DEV_ID = T2.DEV_ID
			LEFT OUTER JOIN dbo.T_DEVIS_DEV AS T1B ON (T1.DEV_ID_SRC = T1B.DEV_ID) 
			INNER JOIN dbo.T_DEV_STYPE_DST AS T6 ON (T1.DST_ID = T6.DST_ID)
			INNER JOIN dbo.T_DEV_ETAT_DET AS T5 ON (T1.DET_ID = T5.DET_ID)
			LEFT OUTER JOIN dbo.T_AFFAIRE_AFF AS T3 ON T1.AFF_ID = T3.AFF_ID
			LEFT OUTER JOIN dbo.V_TIERS_LK AS T4 ON T3.TIE_ID = T4.TIE_ID
			LEFT OUTER JOIN dbo.V_TIERS_LK AS T7 ON T1.TIE_ID = T7.TIE_ID

**SQLpro** · 26/10/2009, 17h24

Je pense que vous n'avez pas compris l'intérêt des CTE.

En effet votre CTE1 ne comporte aucune jointure ni aucun filtre.

Votre CTE2 faite sur CTE1 ne comporte aucune jointure ni aucun filtre, mais oblige à déjà calculer les données de la CTE1 !

Votre CTE3 faite sur CTE2 ne comporte aucune jointure ni aucun filtre, mais oblige à déjà calculer les données de la CTE2, qui déjà calcule les données de la CTE1 !!!

Bref en remettant tout cela dans une seule requête sans CTE et en plaçant les bons index, vous irez sans doute beaucoup plus vite !

A +

**PickEpique** · 26/10/2009, 17h57

Je pense que vous n'avez pas compris l'intérêt des CTE

Oui certainement, ainsi je pensais notamment que les CTE me permettraient d'étager mes calculs en ligne
1 je ramasse tout les colonnes dont j'ai besoin dans mes calculs
2 je commence a calculer mes colonnes à partir des champs de 1
3 je continue à partir des champs calculés en 2
4 je joint ces lignes calculées au reste de ma table
je pensais aussi que la jointure en final suffisait à ce que ma vue, (car c'est dans une vue) utilise les bons index

Code :

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

1
2
 
FROM dbo.T_DEVIS_DEV AS T1 INNER JOIN CTE3

Quand je l'utilise comme cela
SELECT ma_liste_de_champ from V_DEVIS_ALL
WHERE DEV_ID = 15600

**PickEpique** · 26/10/2009, 18h57

Pourtant SQLpro il me semblait que vous m'ayez conseillé un truc semblable il y a peu
http://www.developpez.net/forums/d36...culee-cascade/

C'est pour un update, mais il semble que le principe est le même non ? ou alors comme vous dites je n'ai vraiment rien compris aux CTE

lenteur d'une requète CTE

MS SQL Server

Vue hybride

Discussions similaires

Partager

Partager