rendre une vue plus rapide

**tortuegenie** · 27/06/2011, 14h13

Bonjour j'ai une vue avec une requete faisant des jointures sur presque toutes les tables de la base (20) et elle dure 10 minutes á executer.

Je voulais savoir s'il existait une solution pour rendre la vue plus rapide par rapport á ces 10 minutes

**aieeeuuuuu** · 27/06/2011, 14h20

Bonjour,

il existe certainement un moyen, je dirai même qu'il en existe surement plusieurs. Mais pour cela, il faudrait qu'on ait au moins la définition de la vue.

La façon dont vous interrogez la vue ensuite peut également jouer.
si, "pour tester", vous faites un
SELECT * FROM MaVue, il est évident que cela mettra plus de temps que si vous sélectionnez seulement les colonnes nécessaires, et que vous filtrez également au maximum.

Donnez nous un peu plus d'info sur votre vue et ce que vous voulez faire

**tortuegenie** · 27/06/2011, 14h29

les champs a afficher sont bien précis.
Cette vue est utilisée pour pouvoir faire une table pivot sous excel.
voici la requete si cela peut vous aider.

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
 
SELECT dbo.StructGlobalForm.StructGlobalForm_StructName, dbo.StructSegment.StructSegment_Name, dbo.StructBrand.StructBrand_Name, dbo.Brand.Brand_Name, 
               dbo.Franchise.Franchise_Name AS BrandFranchise, dbo.Segment.Segment_Name, dbo.StructCategory.StructCategory_Name, dbo.Sale.Sale_GSExSeminar, 
               dbo.Sale.Sale_NSNLExSeminar, dbo.Sale.Sale_CalcNSNLExSeminar, dbo.Customer.Customer_Key, dbo.Customer.Customer_Name, 
               dbo.Customer.Customer_IsFinancing, dbo.Customer.Customer_IsLicense, dbo.Customer.Customer_IsAtRisk, dbo.Customer.Customer_IsLost, 
               dbo.Customer.Customer_IsSchool, dbo.Customer.Customer_IsShop, dbo.Customer.Customer_IsNewAccountFY0910, 
               dbo.Customer.Customer_IsNewAccountFY1011, dbo.Customer.Customer_IsNewAccountFY1112, dbo.Customer.Customer_FYTDGrowingVsLastFY, 
               dbo.Customer.Customer_ContractTarget, dbo.Customer.Customer_MonthsNotOrdered, Month_1.Month_FY AS FirstOrderMonthFY, 
               Month_1.Month_FYMonth AS FirstOrderMonthFYMonth, Month_2.Month_FY AS LastOrderMonthFY, Month_2.Month_FYMonth AS LastOrderMonthFYMonth, 
               Month_3.Month_FY AS NewFirstOrderMonthFY, Month_3.Month_FYMonth AS NewFirstOrderMonthFYMonth, 
               dbo.CustomerClassification.CustomerClassification_Name, dbo.CustomerTopAccount.CustomerTopAccount_Key, 
               dbo.CustomerTopAccount.CustomerTopAccount_Text, dbo.District.District_Key, dbo.District.District_Name, dbo.[Group].Group_Key, dbo.[Group].Group_Name, 
               dbo.Office.Office_Key, dbo.Office.Office_Name, dbo.Country.Country_Name, dbo.SalesRep.SalesRep_Key, dbo.SalesRep.SalesRep_Name, 
               dbo.KeyAccountDistrict.KADistrict_Key, dbo.KeyAccountDistrict.KADistrict_Name, dbo.KeyAccountRep.KARep_Key, dbo.KeyAccountRep.KARep_Name, 
               dbo.KeyAccountOffice.KAOffice_Key, dbo.KeyAccountOffice.KAOffice_Name, dbo.KeyAccountGroup.KAGroup_Key, dbo.KeyAccountGroup.KAGroup_Name, 
               dbo.Month.Month_FY, dbo.Month.Month_FYMonth, dbo.Month.Month_FYQuarter, dbo.Month.Month_12MRolling, dbo.TradeChannel.TradeChannel_Name, 
               dbo.Segmentation1.Segmentation1_Name AS Segmentation1Base, Segmentation1_1.Segmentation1_Name AS Segmentation112MRolling, 
               Segmentation1_2.Segmentation1_Name AS Segmentation1Projected
FROM  dbo.Sale INNER JOIN
               dbo.StructGlobalForm ON dbo.Sale.Sale_StructGlobalForm_ID = dbo.StructGlobalForm.StructGlobalForm_ID INNER JOIN
               dbo.StructSegment ON dbo.StructGlobalForm.StructGlobalForm_StructSegment_ID = dbo.StructSegment.StructSegment_ID INNER JOIN
               dbo.StructBrand ON dbo.StructSegment.StructSegment_StructBrand_ID = dbo.StructBrand.StructBrand_ID INNER JOIN
               dbo.StructCategory ON dbo.StructBrand.StructBrand_StructCategory_ID = dbo.StructCategory.StructCategory_ID INNER JOIN
               dbo.Brand ON dbo.StructBrand.StructBrand_Brand_ID = dbo.Brand.Brand_ID INNER JOIN
               dbo.Franchise ON dbo.Brand.Brand_Franchise_ID = dbo.Franchise.Franchise_ID INNER JOIN
               dbo.Segment ON dbo.StructSegment.StructSegment_Segment_ID = dbo.Segment.Segment_ID INNER JOIN
               dbo.Customer ON dbo.Sale.Sale_Customer_ID = dbo.Customer.Customer_ID INNER JOIN
               dbo.Month ON dbo.Sale.Sale_Month_ID = dbo.Month.Month_ID INNER JOIN
               dbo.Month AS Month_1 ON dbo.Customer.Customer_FirstOrder_Month_ID = Month_1.Month_ID INNER JOIN
               dbo.Month AS Month_2 ON dbo.Customer.Customer_LastOrder_Month_ID = Month_2.Month_ID INNER JOIN
               dbo.Month AS Month_3 ON dbo.Customer.Customer_FirstOrderMonthFY_ID = Month_3.Month_ID INNER JOIN
               dbo.CustomerClassification ON dbo.Customer.Customer_CustomerClassification_ID = dbo.CustomerClassification.CustomerClassification_ID INNER JOIN
               dbo.CustomerTopAccount ON dbo.Customer.Customer_CustomerTopAccount_ID = dbo.CustomerTopAccount.CustomerTopAccount_ID INNER JOIN
               dbo.District ON dbo.Sale.Sale_District_ID = dbo.District.District_ID INNER JOIN
               dbo.[Group] ON dbo.District.District_Group_ID = dbo.[Group].Group_ID INNER JOIN
               dbo.Office ON dbo.[Group].Group_Office_ID = dbo.Office.Office_ID INNER JOIN
               dbo.Country ON dbo.Office.Office_Country_ID = dbo.Country.Country_ID INNER JOIN
               dbo.SalesRep ON dbo.District.District_SalesRep_ID = dbo.SalesRep.SalesRep_ID INNER JOIN
               dbo.KeyAccountDistrict ON dbo.Customer.Customer_KADistrict_ID = dbo.KeyAccountDistrict.KADistrict_ID INNER JOIN
               dbo.KeyAccountRep ON dbo.KeyAccountDistrict.KADistrict_KARep_ID = dbo.KeyAccountRep.KARep_ID INNER JOIN
               dbo.KeyAccountGroup ON dbo.KeyAccountDistrict.KADistrict_KAGroup_ID = dbo.KeyAccountGroup.KAGroup_ID INNER JOIN
               dbo.KeyAccountOffice ON dbo.KeyAccountGroup.KAGroup_KAOffice_ID = dbo.KeyAccountOffice.KAOffice_ID INNER JOIN
               dbo.TradeChannel ON dbo.Sale.Sale_TradeChannel_ID = dbo.TradeChannel.TradeChannel_ID INNER JOIN
               dbo.Segmentation1 ON dbo.Customer.Customer_Segmentation1_ID = dbo.Segmentation1.Segmentation1_ID INNER JOIN
               dbo.Segmentation1 AS Segmentation1_1 ON dbo.Customer.Customer_Segmentation12MRolling_ID = Segmentation1_1.Segmentation1_ID INNER JOIN
               dbo.Segmentation1 AS Segmentation1_2 ON dbo.Customer.Customer_Projected_Segmentation_ID = Segmentation1_2.Segmentation1_ID

Invité · 27/06/2011, 15h08

Il vous reste à nous communiquer votre version de bd, la volumétrie, la DDL des tables, les indexes et le plan d’exécution...
http://www.developpez.net/forums/d96...vement-poster/

**elsuket** · 27/06/2011, 15h13

Bonjour,

Il est impossible d'optimiser cette vue sans savoir :

- quelles sont les requêtes qui l'utilisent, et leur plan d'exécution
- quels sont les index sur les tables qui participent à la requête.

Après un petit débroussaillage, on obtient :

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
 SELECT		SGF.StructGlobalForm_StructName
		, SS.StructSegment_Name
		, SB.StructBrand_Name
		, B.Brand_Name 
		, F.Franchise_Name AS BrandFranchise
		, SEG.Segment_Name
		, SC.StructCategory_Name
		, S.Sale_GSExSeminar
		, S.Sale_NSNLExSeminar
		, S.Sale_CalcNSNLExSeminar
		, C.Customer_Key
		, C.Customer_Name
		, C.Customer_IsFinancing
		, C.Customer_IsLicense
		, C.Customer_IsAtRisk
		, C.Customer_IsLost
		, C.Customer_IsSchool
		, C.Customer_IsShop
		, C.Customer_IsNewAccountFY0910
		, C.Customer_IsNewAccountFY1011
		, C.Customer_IsNewAccountFY1112
		, C.Customer_FYTDGrowingVsLastFY
		, C.Customer_ContractTarget
		, C.Customer_MonthsNotOrdered
		, M1.Month_FY AS FirstOrderMonthFY
		, M1.Month_FYMonth AS FirstOrderMonthFYMonth
		, M2.Month_FY AS LastOrderMonthFY
		, M2.Month_FYMonth AS LastOrderMonthFYMonth
		, M3.Month_FY AS NewFirstOrderMonthFY
		, M3.Month_FYMonth AS NewFirstOrderMonthFYMonth
		, CC.CustomerClassification_Name
		, CTA.CustomerTopAccount_Key
		, CTA.CustomerTopAccount_Text
		, D.District_Key
		, D.District_Name
		, G.Group_Key
		, G.Group_Name
		, O.Office_Key
		, O.Office_Name
		, CY.Country_Name
		, SR.SalesRep_Key
		, SR.SalesRep_Name
		, KAD.KADistrict_Key
		, KAD.KADistrict_Name
		, KAR.KARep_Key
		, KAR.KARep_Name
		, KAO.KAOffice_Key
		, KAO.KAOffice_Name
		, KAG.KAGroup_Key
		, KAG.KAGroup_Name
		, M.Month_FY
		, M.Month_FYMonth
		, M.Month_FYQuarter
		, M.Month_12MRolling
		, TC.TradeChannel_Name, 
		, SEG1.Segmentation1_Name AS Segmentation1Base
		, SEG1_1Segmentation1_Name AS Segmentation112MRolling
		, SEG1_2.Segmentation1_Name AS Segmentation1Projected
FROM		dbo.Sale AS S
INNER JOIN	dbo.StructGlobalForm AS SGF
			ON S.Sale_StructGlobalForm_ID = dbo.StructGlobalForm.StructGlobalForm_ID 
INNER JOIN	dbo.StructSegment AS SS
			ON dbo.StructGlobalForm.StructGlobalForm_StructSegment_ID = SS.StructSegment_ID 
INNER JOIN	dbo.StructBrand AS SB
			ON SS.StructSegment_StructBrand_ID = SB.StructBrand_ID 
INNER JOIN	dbo.StructCategory AS SC
			ON SB.StructBrand_StructCategory_ID = SC.StructCategory_ID 
INNER JOIN	dbo.Brand AS B
			ON SB.StructBrand_Brand_ID = B.Brand_ID 
INNER JOIN	dbo.Franchise AS F
			ON B.Brand_Franchise_ID = F.Franchise_ID 
INNER JOIN	dbo.Segment AS SEG
			ON SS.StructSegment_Segment_ID = SEG.Segment_ID 
INNER JOIN	dbo.Customer AS C
			ON S.Sale_Customer_ID = C.Customer_ID 
INNER JOIN	dbo.Month AS M
			ON S.Sale_Month_ID = M.Month_ID 
INNER JOIN	dbo.Month AS M1
			ON C.Customer_FirstOrder_Month_ID = M1.Month_ID 
INNER JOIN	dbo.Month AS M2
			ON C.Customer_LastOrder_Month_ID = M2.Month_ID 
INNER JOIN	dbo.Month AS M3
			ON C.Customer_FirstOrderMonthFY_ID = M3.Month_ID 
INNER JOIN	dbo.CustomerClassification AS CC
			ON C.Customer_CustomerClassification_ID = CC.CustomerClassification_ID 
INNER JOIN	dbo.CustomerTopAccount AS CTA
			ON C.Customer_CustomerTopAccount_ID = CTA.CustomerTopAccount_ID 
INNER JOIN	dbo.District AS D
			ON S.Sale_District_ID = D.District_ID 
INNER JOIN	dbo.[GROUP] AS G
			ON D.District_Group_ID = G.Group_ID 
INNER JOIN	dbo.Office AS O
			ON G.Group_Office_ID = O.Office_ID 
INNER JOIN	dbo.Country AS CY
			ON O.Office_Country_ID = CY.Country_ID 
INNER JOIN	dbo.SalesRep AS SR
			ON D.District_SalesRep_ID = SR.SalesRep_ID 
INNER JOIN	dbo.KeyAccountDistrict AS KAD
			ON C.Customer_KADistrict_ID = KAD.KADistrict_ID 
INNER JOIN	dbo.KeyAccountRep AS KAR
			ON KAD.KADistrict_KARep_ID = KAR.KARep_ID 
INNER JOIN	dbo.KeyAccountGroup AS KAG
			ON KAD.KADistrict_KAGroup_ID = KAG.KAGroup_ID 
INNER JOIN	dbo.KeyAccountOffice AS KAO
			ON KAG.KAGroup_KAOffice_ID = KAO.KAOffice_ID 
INNER JOIN	dbo.TradeChannel AS TC
			ON S.Sale_TradeChannel_ID = TC.TradeChannel_ID 
INNER JOIN	dbo.Segmentation1 AS SEG1
			ON C.Customer_Segmentation1_ID = SEG1.Segmentation1_ID 
INNER JOIN	dbo.Segmentation1 AS SEG1_1
			ON C.Customer_Segmentation12MRolling_ID = SEG1_1Segmentation1_ID 
INNER JOIN	dbo.Segmentation1 AS SEG1_2
			ON C.Customer_Projected_Segmentation_ID = SEG1_2.Segmentation1_ID

Dans l'attente des plans de requête

@++

**tortuegenie** · 27/06/2011, 15h25

Malheureusement je ne sais pas ce qu'est un plan de requete.

Tout ce que je sais, c'est que cette vue est utilisée par excel afin de générer une table Pivot.

Les index ont été placé sur chaque clé (id) et sur 2-3 et sur toutes les foreign-key.

En esperant que cela vous ai aidé ?

**tortuegenie** · 27/06/2011, 15h26

j'oubliais SQLserver 2005 express

rendre une vue plus rapide

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager