Index XML : lenteur

**ariesnojf** · 07/10/2010, 18h25

Bonjour à tous,

Je reste perplexe sur un événement qui vient de m'arriver et j'attends de vous, SVP, d'éclairer ma lanterne.

J'ai une première requête utilisant un champ XML, qui pour l'éxecution prenait du temps. Je décide de mettre en place un index sur ce champ XML, je gagne à peu prés 20% de temps.
Cependant, j'ai une seconde requête, toujours avec ce champ en selection (mais cette requête est beaucoup plus complexe que la 1ière utilisant des pivots, des outer apply, ...( heu c'est pas moi qui ai fait cela)) qui prend 2'50 à s'éxécuter avec champ XML indexé alors qu'en supprimant l'index, je passe à 15 secondes.

Je pensais qu'un index ordonnait les données d'un champ de façon à optimiser ce champ et réduire le temps de recherche lors de requête.
Or dans mon cas, c'est plutôt le contraire. Y a t il une subtilité des index sur des champs XML ?

J'ai fouiné un peu partout et mis à par le technet de microsoft, avec les précautions d'usage, je n'ai rien trouvé.

Merci d'avance pour vos réponses.

SQL server 2005
WS2003

**sevyc64** · 07/10/2010, 20h30

As-tu effectuer un recalcul des index ????

Car, d'après ce qui m'avait été expliqué, après la création et jusqu'au prochain recalcul, les index n'indexent que les enregistrements créés ou modifiés.

ATTENTION, un recalcul des index est une opération de maintenance lourde qui peut durer plusieurs heures

**kagemaru** · 07/10/2010, 21h29

bonjour,

Pour y voir plus clair, l'idéal serait de poster le code de la requête, la structure des tables et des index. En complément, le plan d'exécution de la requête et le coût (set showplan_text on + set statistics io on) permettrait d'apporter encore plus de clarté.

merci

**ariesnojf** · 08/10/2010, 09h34

Bonjour ci joint requete :

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
 
select 
Season
, Month
,Place as 'place'
, Office as 'Name Office'
, sum([c]) as Nombre
, sum([c2FRI]) as NombreFRI
, sum([c2Re1]) + sum([c2Re2]) + sum([c2Re3]) as NombreRe
, sum([oFRI]) as 'Ordered Qty'
, sum([c2FRI]) as 'Number of orders'
, sum([pFRI]) as 'Shipped Qty'
, sum([ic]) +  sum([oc]) as 'Total Cost'
, (sum([ic]) +  sum([oc])) / sum([pFRI]) as 'Cost/Shipped'
, sum([oFRI])/sum([c2FRI]) as 'Average of pieces per order'
, sum([ACCEPTEDxx]) as 'Quantity of final  accepted by '
, sum([Accepted]) as 'Quantity of final inspections accepted by '
from (
SELECT 
gs.Season Season 
, T23.f.value('.','varchar(50)') as xmlSeason
, Performed
, cast(year(Performed) as varchar(4)) + '-' +  right('0' + cast(month(Performed) as varchar(2)),2) as Month
, j.ClientRef
, Ref
, Ref as ir
, Ref as ir2
, Ref as ir3
, Ref as ir4
, Ref as ir5
, Ref as ir6
, T31.f.value('.','varchar(50)') as Number
 
, T7.f.value('.','varchar(50)') as AGENT
, T6.f.value('.','varchar(50)') as SUPPLIER
, T22.f.value('.','varchar(50)') as Factory
, T20.f.value('.','varchar(50)') as Ordernumber
, T15.f.value('.','varchar(50)') as ReportNumber
, cast(replace(T8.f.value('.','varchar(50)'),' ','') as int) as OrderQty
, cast(replace(T9.f.value('.','varchar(50)'),' ','') as int) as ProducedQty
, cast(replace(T30.f.value('.','varchar(50)'),' ','') as int) as ControlledQty
, Cost, Currency
, OtherCost, CurrencyOther
, IsValidate
 
, CASE IsValidate
         WHEN 0 THEN 'Rejected'
         WHEN 1 THEN 'Accepted'
         WHEN 9 THEN 'Abortive'
         ELSE '?'
      END as Decision
, CASE IsValidate
         WHEN 0 THEN 'Rejected' + replace(T11.f.value('.','varchar(50)'), ' ', '')
         WHEN 1 THEN 'Accepted' + replace(T11.f.value('.','varchar(50)'), ' ', '')
         WHEN 9 THEN 'Abortive' + replace(T11.f.value('.','varchar(50)'), ' ', '')
         ELSE '?'
      END as Decision2
, 'C' + V1.f.value('.','varchar(50)') + replace(T11.f.value('.','varchar(50)'), ' ', '')
as ValC
, 'L' + V2.f.value('.','varchar(50)') + replace(T11.f.value('.','varchar(50)'), ' ', '')
as ValL
, 'M' + V3.f.value('.','varchar(50)') + replace(T11.f.value('.','varchar(50)'), ' ', '')
as ValM
, 'W' + V4.f.value('.','varchar(50)') + replace(T11.f.value('.','varchar(50)'), ' ', '')
as ValW
, T12.f.value('.','varchar(50)') as Inspectiondate2
, T13.f.value('.','varchar(50)')+ 'xx' as finaljudgement2
, T14.f.value('.','varchar(50)') as prodtype
, T16.f.value('.','varchar(50)') as headoffice
, T17.f.value('.','varchar(50)') as Office
, T18.f.value('.','varchar(50)') as type
, T19.f.value('.','varchar(50)') as Stylename
, T21.f.value('.','varchar(50)') as Colournumber
 
, T24.f.value('.','varchar(50)') as Sendingdate
, T25.f.value('.','varchar(50)') as conformitydecision
, T26.f.value('.','varchar(50)') as packingdecision
, T27.f.value('.','varchar(50)') as workmanshipdecision
, T28.f.value('.','varchar(50)') as measdecision
, T29.f.value('.','varchar(50)') as Place
, V1.f.value('.','varchar(50)') as Conformity_CheckActive
, V2.f.value('.','varchar(50)') as Packing_CheckActive
, V3.f.value('.','varchar(50)') as Measurement_CheckActive
, V4.f.value('.','varchar(50)') as Workmanship_CheckActive
 
, replace('o' + T11.f.value('.','varchar(50)'), ' ', '') as Number2
, replace('p' + T11.f.value('.','varchar(50)'), ' ', '') as Number3
, replace('d' + T11.f.value('.','varchar(50)'), ' ', '') as Number4
, replace('f' + T11.f.value('.','varchar(50)'), ' ', '') as Number5
, replace('r' + T11.f.value('.','varchar(50)'), ' ', '') as Number6
/*, replace('c' + T11.f.value('.','varchar(50)'), ' ', '') as Number7*/
, 'c' as Number7
, replace('s' + T11.f.value('.','varchar(50)'), ' ', '') as Number8
, replace('wd' + T11.f.value('.','varchar(50)'), ' ', '') as Number9
, replace('md' + T11.f.value('.','varchar(50)'), ' ', '') as Number10
, replace('cd' + T11.f.value('.','varchar(50)'), ' ', '') as Number11
, replace('pd' + T11.f.value('.','varchar(50)'), ' ', '') as Number12
/*, replace('ic' + T11.f.value('.','varchar(50)'), ' ', '') as Number13
, replace('oc' + T11.f.value('.','varchar(50)'), ' ', '') as Number14*/
, 'ic' as Number13
, 'oc' as Number14
 
, replace('icur' + T11.f.value('.','varchar(50)'), ' ', '') as Number15
, replace('ocur' + T11.f.value('.','varchar(50)'), ' ', '') as Number16
, replace('val' + T11.f.value('.','varchar(50)'), ' ', '') as Number17
, replace('c2' + T11.f.value('.','varchar(50)'), ' ', '') as Number18
, replace('a' + T11.f.value('.','varchar(50)'), ' ', '') as Number19
FROM job j
left join GroupeSeason gs on j.Season = gs.id
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Supplier"]') as T6(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Agent"]') as T7(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Orderedquantity"]') as T8(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Producedquantity"]') as T9(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="number"]') as T11(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="date"]') as T12(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="finaljudgement"]') as T13(f)
 
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="prodtype"]') as T14(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="ReportNumber"]') as T15(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="headoffice"]') as T16(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Office"]') as T17(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="type"]') as T18(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Stylename"]') as T19(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Ordernumber"]') as T20(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Colournumber"]') as T21(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Factory"]') as T22(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Season"]') as T23(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Sendingdate"]') as T24(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="conformitydecision"]') as T25(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="packingdecision"]') as T26(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="workmanshipdecision"]') as T27(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="measdecision"]') as T28(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="Place"]') as T29(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="ControlledQuantity"]') as T30(f)
OUTER APPLY JobData.nodes('/report/fields/FIELD[@fname="number"]') as T31(f)
 
OUTER APPLY JobCustomerValidation.nodes('/actions/action/FIELD[@fname="Conformity_CheckActive"]') as V1(f)
OUTER APPLY JobCustomerValidation.nodes('/actions/action/FIELD[@fname="Packing_CheckActive"]') as V2(f)
OUTER APPLY JobCustomerValidation.nodes('/actions/action/FIELD[@fname="Measurement_CheckActive"]') as V3(f)
OUTER APPLY JobCustomerValidation.nodes('/actions/action/FIELD[@fname="Workmanship_CheckActive"]') as V4(f)
 
where JobData.exist('/report/fields/FIELD[.="xx"]') = 1 
and  JobData.exist('/report/fields/FIELD[@fname="number"][.="Partial re "]') = 0 
and  JobData.exist('/report/fields/FIELD[@fname="number"][.="Abortive"]') = 0 
and  JobData.exist('/report/fields/FIELD[@fname="headoffice"][.="xx"]') = 0
 
and DATEDIFF(month, Performed, GETDATE()) = 1   
 
/* And IsValidate is not NULL   */
) as SourceTable 
PIVOT
(
SUM(OrderQty)
FOR Number2 in ([oFRI],[oRe1],[oRe2],[oRe3])
) as p1
PIVOT
(
SUM(ProducedQty)
FOR Number3 in ([pFRI],[pRe1],[pRe2],[pRe3])
) as p2
PIVOT
(
SUM(Cost)
FOR Number13 in ([ic])
) as p2
PIVOT
(
SUM(OtherCost)
FOR Number14 in ([oc])
) as p3
PIVOT
(
count(Ref)
FOR Number7 in ([c])
) as p4
PIVOT
(
count(ir)
FOR Number18 in ([c2FRI],[c2Re1],[c2Re2],[c2Re3])
) as p5
PIVOT
(
count(ir2)
FOR Decision in ([Accepted])
) as p6
PIVOT
(
count(ir3)
FOR finaljudgement2 in ([ACCEPTED])
) as p7
 
Group by 
Season
, Month
,Place
, Office

Rq: Pour des raisons de confidentialité, j'ai dû renommer quelque champs, et quelque variables ...

Concernant la table contenant le champ XML :

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
 
	[Id] [uniqueidentifier] NOT NULL,
	[ClientRef] [nvarchar](max) NULL,
	[xxRef] [nvarchar](max) NULL,
	[Season] [nvarchar](max) NULL,
	[OrderReceivedOn] [datetime] NULL,
	[PlantInformed] [datetime] NULL,
	[Scheduled] [datetime] NULL,
	[DateConfirmed] [datetime] NULL,
	[Performed] [datetime] NULL,
	[RefSampleAskedOn] [datetime] NULL,
	[RefSampleReceivedOn] [datetime] NULL,
	[OrderNumber] [nvarchar](max) NULL,
	[ReportNumber] [nvarchar](max) NULL,
	[Type] [nvarchar](50) NOT NULL CONSTRAINT [DF_Job_Type]  DEFAULT ((0)),
	[Number] [nvarchar](50) NOT NULL CONSTRAINT [DF_Job_Number]  DEFAULT ((0)),
	[Conclusion] [nvarchar](50) NULL,
	[CertifDeliv] [bit] NOT NULL CONSTRAINT [DF_Job_CertifDeliv]  DEFAULT ((0)),
	[SupplierId] [uniqueidentifier] NOT NULL,
	[StatusId] [uniqueidentifier] NOT NULL,
	[GroupeId] [uniqueidentifier] NOT NULL,
	[EnseigneId] [uniqueidentifier] NULL,
	[IsAddressOk] [nvarchar](50) NOT NULL,
	[UserId] [uniqueidentifier] NULL,
	[IsValidate] [nvarchar](max) NULL,
	[Cost] [float] NULL,
	[Currency] [nvarchar](50) NULL,
	[OtherCost] [float] NULL,
	[CurrencyOther] [nvarchar](50) NULL,
	[NotInvoicedToCustomer] [bit] NULL CONSTRAINT [DF_Job_NotInvoicedToCustomer]  DEFAULT ((0)),
	[JobData] [xml] NULL,
	[JobCustomerValidation] [xml] NULL,
	[ValidationDate] [datetime] NULL,
	[ValidationUser] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[CreatedBy] [nvarchar](50) NULL,
	[ModifiedOn] [datetime] NULL,
	[ModifiedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED

Je met l'index sur JobData ...

Merci à vous

**mikedavem** · 08/10/2010, 09h41

Avez vous ajouter un index XML secondaire de type PATH pour essayer ?

++

**ariesnojf** · 08/10/2010, 10h55

Bonjour,

Non, pas testé.

Je vais le faire en début am. Je n'ai jamais pratiqué ce type d'index ...
Effectivement, aprés avoir fouiné, et dans la requête utilisant la méthode exist, cela peut peut être changé les choses.

Le fait d'avoir un champ xml, considéré comme un blob (j'espère pas dire de bêtise), peut être plus ou moins volumineux. L'index primaire se mélangerait il pas les pinceaux si le champ XML est volumineux ce qui entraînerai des performances plutôt inattendue ?

Pour terminer, c'est assez "vicieux" le fait de créer un autre index basé sur un index primaire et le champ, y a t il le même principe chez Oracle ?

Merci

**ariesnojf** · 08/10/2010, 13h50

Envoyé par mikedavem

Avez vous ajouter un index XML secondaire de type PATH pour essayer ?

++

Nickel,

avec les deux index, je passe à 11", mieux que sans index, et cela me permet de garder cette perf pour ma première requête.

En tous cas, je vais approfondir ce type d'index pour mieux les appréhender.