rendre une vue plus rapide

Version imprimable

27/06/2011, 14h13
tortuegenie

rendre une vue plus rapide

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
27/06/2011, 14h20
aieeeuuuuu

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

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:

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

27/06/2011, 15h08
Invité

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/

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:

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 ;)

@++ ;)

27/06/2011, 15h25
tortuegenie

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é ?
27/06/2011, 15h26
tortuegenie

j'oubliais SQLserver 2005 express
28/06/2011, 08h44
WOLO Laurent
Vous pouvez executer ceci :
Code:

1 2 3 4 5 SET SHOWPLAN_XML ON go --Votre requête ici go SET SHOWPLAN_XML OFF
Vous aurez une sortie de ce type que vous pouvez enregistrer dans un fichier xml .sqlplan

Code:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="exec [dbo].[Bon_Reception_SelectByDep_Design] 'BOPAKA'
" StatementId="1" StatementCompId="1" StatementType="EXECUTE PROC"><StoredProc ProcName="dbo.Bon_Reception_SelectByDep_Design"><Statements><StmtSimple StatementText="CREATE procedure [dbo].[Bon_Reception_SelectByDep_Design]
	@dep_design varchar(50)
as
begin
	select UserName as Rec_UserID,Rec_id,Rec_Ref,Rec_date,dep_design
	from dbo.Bon_Reception B left 
		Join dbo.aspnet_Users U On B.Rec_UserID=U.UserID
		join Depot D on D.Dep_id=B.Dep_id
		order by rec_id
" StatementId="2" StatementCompId="3" StatementType="SELECT" StatementSubTreeCost="0.0101501" StatementEstRows="2" StatementOptmLevel="FULL" QueryHash="0xB9446B0E170F9F39" QueryPlanHash="0x616A7531F7FFD3CF" StatementOptmEarlyAbortReason="GoodEnoughPlanFound"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="24" CompileTime="31" CompileCPU="31" CompileMemory="168"><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="2" EstimateIO="0" EstimateCPU="4.18e-005" AvgRowSize="322" EstimatedTotalSubtreeCost="0.0101501" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_id"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_Ref"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_date"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[aspnet_Users]" Alias="[U]" Column="UserName"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Depot]" Alias="[D]" Column="Dep_design"/></OutputList><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[DB_STOCK].[dbo].[Bon_Reception].[Rec_UserID] as [B].[Rec_UserID]=[DB_STOCK].[dbo].[aspnet_Users].[UserId] as [U].[UserId]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_UserID"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[aspnet_Users]" Alias="[U]" Column="UserId"/></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2" EstimateIO="0" EstimateCPU="8.36e-006" AvgRowSize="80" EstimatedTotalSubtreeCost="0.00673376" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_id"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_Ref"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_date"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_UserID"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Depot]" Alias="[D]" Column="Dep_design"/></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Dep_id"/></OuterReferences><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="2" EstimateIO="0.003125" EstimateCPU="0.0001592" AvgRowSize="58" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_id"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_Ref"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_date"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_UserID"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Dep_id"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_id"/></DefinedValue><DefinedValue><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_Ref"/></DefinedValue><DefinedValue><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_date"/></DefinedValue><DefinedValue><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Rec_UserID"/></DefinedValue><DefinedValue><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Dep_id"/></DefinedValue></DefinedValues><Object Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Index="[PK__Bon_Rece__81BFDDA25FB337D6]" Alias="[B]" IndexKind="Clustered"/></IndexScan></RelOp><RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="36" EstimatedTotalSubtreeCost="0.0034412" TableCardinality="9" Parallel="0" EstimateRebinds="0" EstimateRewinds="1"><OutputList><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Depot]" Alias="[D]" Column="Dep_design"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Depot]" Alias="[D]" Column="Dep_design"/></DefinedValue></DefinedValues><Object Database="[DB_STOCK]" Schema="[dbo]" Table="[Depot]" Index="[PK__Depot__0C2841550425A276]" Alias="[D]" IndexKind="Clustered"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Depot]" Alias="[D]" Column="Dep_id"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[DB_STOCK].[dbo].[Bon_Reception].[Dep_id] as [B].[Dep_id]"><Identifier><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[Bon_Reception]" Alias="[B]" Column="Dep_id"/></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="5" EstimateIO="0.0032035" EstimateCPU="8.4e-005" AvgRowSize="283" EstimatedTotalSubtreeCost="0.0033715" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="1"><OutputList><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[aspnet_Users]" Alias="[U]" Column="UserId"/><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[aspnet_Users]" Alias="[U]" Column="UserName"/></OutputList><IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[aspnet_Users]" Alias="[U]" Column="UserId"/></DefinedValue><DefinedValue><ColumnReference Database="[DB_STOCK]" Schema="[dbo]" Table="[aspnet_Users]" Alias="[U]" Column="UserName"/></DefinedValue></DefinedValues><Object Database="[DB_STOCK]" Schema="[dbo]" Table="[aspnet_Users]" Index="[aspnet_Users_Index]" Alias="[U]" IndexKind="Clustered"/></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></StoredProc></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
29/06/2011, 04h05
elsuket

Bonjour,

Un plan de requête est ce que SQL Server calcule avant d'exécuter pour la première fois une requête, de façon à l'exécuter le plus rapidement possible.

Il est exposé :

1. soit comme vous le propose WOLO Laurent, ce qui sera pour nous le meilleur moyen de vous aider
2. soit en activant la capture du plan d'exécution réel :

http://img829.imageshack.us/img829/2...cutionplan.jpg
qui montre le plan d'exécution sous forme graphique.
En effectuant un clic-droit sur celui-ci, vous pouvez le sauvegarder comme document XML
3. soit sous forme d'un document XML, comme en 1., à l'aide de la vue sys.dm_exec_query_plans

@++ ;)
29/06/2011, 11h22
WOLO Laurent

Sauf que tortuegenie ne réagit plus.
29/06/2011, 12h46
SQLpro

1) les règles d'intégrité référentielles ont-elles toutes été mise en place (contraintes Foreign Key) ?
2) pouvez vous remplacer cette table par une vue indexée ?
(dans ce cas, ajouter les préfixes de schéma des tables que vous avez oublié, et créez là en schéma binding.)

A +
29/06/2011, 22h39
iberserk

+1 pour le point 1...

Citation:

pouvez vous remplacer cette table par une vue indexée ?

Citation:

des jointures sur presque toutes les tables de la base

mmm... donc peut-être plus que doubler la taille de sa base de donnée? d'autant que la moindre modification d'une des tables (sachant qu'elles y sont pour ainsi dire toutes...) entraînera la maintenance de la vue?

La question est: a quelle fréquence est utilisée cette vue?
Les données ciblées sont'elle temps réel ou peut'on prévoir une solution avec un différé de 6/12 heures par exemple (OLAP mis à jour tous les X heures?...).

Enfin, la requête est'elle lourde à exécuter ou longue à afficher? si vous rapatriez 1 giga de données par le réseau une vue indexée ne vous fera pas gagner grand chose, le coût n’étant pas imputable à la requête mais au rapatriement des données par le réseau etc (bon ok 10 minutes ça fait beaucoup mais quel est la qualité du réseau?).