Index XML : lenteur

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. 8O
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

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

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

Bonjour ci joint requete :

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 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 ... :aie:

Concernant la table contenant le champ XML :

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

[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

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

++

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

Re,

Citation:

---

Envoyé par sevyc64

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

---

Effectué, cela ne change rien ...:calim2:

Merci tout de même.

Citation:

---

Envoyé par mikedavem

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

++

---

:ccool: 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.

:merci:

Un index primaire XML équivaut à créer une structure de données, globale. Il ne trie pas les données, mais énumère les objets.
Il faut donc créer des index secondaires XML en fonction des requêtes passée.
En sus, prévoir du XML typé avec une collection de schéma accélérera encore plus les traitement, car les objets sont prévisible.

En particulier pour votre requête je rajouterais un index sur les attributs car vous faites des EXISTS.

A +

Bonjour,

merci pour ce complément d'info,

par contre, quand vous parlez d'attributs, on parle bien des champs de la table ??? (excusez, j'ai l'impression d'être neuneu ...:boulet:)

non, ce sont les attribut XML des documents XML de votre colonne de table de type XML.

Dans une base de données on ne pare pas de champs, mais de colonnes :
http://sqlpro.developpez.com/cours/sqlaz/erreurs/#L2

A +

Pardon,

je fais un abus de langage :oops:, il est vrai qu'il est facile de dériver et de garder de très mauvaises habitudes.

Par contre, ne maîtrisant absolument pas les colonnes XML, l'index dont vous me parlez, s'agit-il d'index secondaire de type Value ?

Non, un index sur PROPERTY.

A +

Merci