Sql Server Query Performance -- Xml Column Shredding -

the below query taking long time return results. have tried can think of optimize query. there around 10000 rows of xml stored structure , taking around 2.5 minutes return results. have set primary xml index on table, , secondary (property) index on table. have set indexes rebuilt every 7 days.

i have referenced these locations

performance optimizations xml data type

guidelines using xml data type methods

xml index guidelines

indexing xml

xml best practices

any advice , suggestions appreciated thanks.

sorry code, can take out if uneeded.

i have table:

create table [dbo].[xmltable](     [xmlid] [int] identity(1,1) not null,     [xmldocument] [xml] not null,  constraint [pk_xmltable] primary key clustered  (     [xmlid] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary] textimage_on [primary] go 

with property secondary index

set arithabort on set concat_null_yields_null on set quoted_identifier on set ansi_nulls on set ansi_padding on set ansi_warnings on set numeric_roundabort off  go  create xml index [ixml_xmltable_xmldocument_property] on [dbo].[xmltable] (     [xmldocument] ) using xml index [pxml_xmltable_xmldocument] property (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) go 

with primary index

set arithabort on set concat_null_yields_null on set quoted_identifier on set ansi_nulls on set ansi_padding on set ansi_warnings on set numeric_roundabort off  go   create primary xml index [pxml_xmltable_xmldocument] on [dbo].[xmltable] (     [xmldocument] )with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) go 

with structure this:

<dev:doc xmlns:dev="http://www.w3.org/2001/xmlschema" schemaversion="0.1" settings="testing" title="ordering">   <dev:base revisionnumber="0" baseid="34433" />   <dev:rev time="2013-01-21t15:08:00">     <dev:person name="me" systemid="54654" />   </dev:rev>   <dev:functions id="a1">     <dev:a1 number="1">       <dev:codes>d</dev:codes>       <dev:required>true</dev:required>       <dev:informational>false</dev:informational>       <dev:visitors>         <dev:visitor name="dev01" location="stlrf">           <dev:divisions>             <dev:division number="1" name="tfr3" usage="monitor">               <dev:description>development fundamentals</dev:description>             </dev:division>             <dev:division number="2" name="def32" usage="monitor">               <dev:description>testing fundamentals</dev:description>             </dev:division>             <dev:division number="3" name="dep13" usage="none">               <dev:description>guided fundamentals</dev:description>             </dev:division>           </dev:divisions>         </dev:visitor>         <dev:visitor name="dev02" location="stlrf">           <dev:divisions>             <dev:division number="1" name="tfr3" usage="monitor">               <dev:description>development fundamentals</dev:description>             </dev:division>             <dev:division number="2" name="def32" usage="monitor">               <dev:description>testing fundamentals</dev:description>             </dev:division>             <dev:division number="3" name="dep13" usage="none">               <dev:description>guided fundamentals</dev:description>             </dev:division>           </dev:divisions>         </dev:visitor>         <dev:visitor name="dev03" location="fgrty">           <dev:divisions>             <dev:division number="1" name="tfr3" usage="monitor">               <dev:description>development fundamentals</dev:description>             </dev:division>             <dev:division number="2" name="def32" usage="monitor">               <dev:description>testing fundamentals</dev:description>             </dev:division>             <dev:division number="3" name="dep13" usage="none">               <dev:description>guided fundamentals</dev:description>             </dev:division>           </dev:divisions>         </dev:visitor>       </dev:visitors>       <dev:senders>         <dev:sender name="fgy(14a)" />       </dev:senders>     </dev:a1>   </dev:functions>   <dev:functions id="a2">     <dev:a2 number="1">       <dev:codes>c</dev:codes>       <dev:required>true</dev:required>       <dev:informational>false</dev:informational>       <dev:remarks>support</dev:remarks>       <dev:notes>ready</dev:notes>       <dev:visitors>         <dev:visitor name="ghff">           <dev:divisions>             <dev:division number="0" name="trial" usage="none">               <dev:fromlocation>lopo</dev:fromlocation>               <dev:tolocation>rdss</dev:tolocation>               <dev:description>rich filter</dev:description>             </dev:division>           </dev:divisions>         </dev:visitor>       </dev:visitors>       <dev:senders>         <dev:sender name="w33r" />       </dev:senders>       <dev:isready>true</dev:isready>       <dev:iscall>false</dev:iscall>     </dev:a2>     <dev:a2 number="2">       <dev:codes>a</dev:codes>       <dev:required>true</dev:required>       <dev:informational>false</dev:informational>       <dev:remarks>loader ready</dev:remarks>       <dev:notes>ready</dev:notes>       <dev:visitors>         <dev:visitor name="udt">           <dev:divisions>             <dev:division number="0" name="trial" usage="none">               <dev:fromlocation>tyuj</dev:fromlocation>               <dev:tolocation>detf</dev:tolocation>               <dev:description>web enhance</dev:description>             </dev:division>           </dev:divisions>         </dev:visitor>       </dev:visitors>       <dev:senders>         <dev:sender name="rj4" />       </dev:senders>       <dev:isready>true</dev:isready>       <dev:iscall>false</dev:iscall>     </dev:a2>   </dev:functions> </dev:doc> 

and query:

;with xmlnamespaces (default 'http://www.w3.org/2001/xmlschema' ) select    a.value('@revisionnumber[1]', 'int') number, b.value('@id[1]', 'varchar(10)') functionid, c.value('@number[1]', 'int') number, d.value('@name[1]', 'varchar(10)') visitor, d.value('@location[1]', 'varchar(10)') location, e.value('@name[1]', 'nvarchar(10)') sender  xmltable x      cross apply xmldocument.nodes('doc/base') aa(a)     cross apply xmldocument.nodes('doc/functions') bb(b)     cross apply b.nodes('*') cc(c)     cross apply c.nodes('visitors/visitor') dd(d)     cross apply c.nodes('senders/sender') ee(e) 

here insert table , testing ran 10000 times

insert xmltable(xmldocument) select * openrowset(    bulk 'c:\users\123\desktop\practice.xml',    single_blob) x;  go 10000 

i query, x3 slower query above

;with xmlnamespaces (default 'http://www.w3.org/2001/xmlschema') select       a.value('(base/@revisionnumber)[1]', 'int') revnumber,     b.value('@id[1]', 'varchar(10)') functionid,     c.value('@number[1]', 'int') number,     d.value('@name[1]', 'varchar(10)') visitor,     d.value('@location[1]', 'varchar(10)') location,     c.value('(senders/sender/@name)[1]', 'varchar(10)') sender xmltable cross apply xmldocument.nodes('doc') aa(a) cross apply a.nodes('functions') bb(b) cross apply b.nodes('*') cc(c) cross apply c.nodes('visitors/visitor') dd(d) 

first off looks doing lot of cross applies shredding. can not extend off single cross apply common denominator , value like:

a.value('/(element)/(element)[(expression)].value('(@thing)', '(type)'). 

my thinking syntax have fine creating heirarchy 5 times differently , cross applying many times. have not done lot of xml shredding know multiple cross applies killer taking 3 dimensional value , populating reference differently 5 times may issue. can extend elements further '/(element)' notation.

i wrong , may fine. if have many cross applies them in temp table dumps first potentially speed up. may able make xml indexes on temp table potentially well. eg:

select a.query('*') #nodea xmltable x     cross apply xmldocument.nodes('doc/base') aa(a) 

then create xml index if performance still slow go away after temp tables removed. not sure on xml indexes though have not done them muct. know can drop , create regular indexes on temp tables assume can xml indexes.

http://msdn.microsoft.com/en-us/library/bb934097.aspx

edit

keep in mind can mix 'query' syntax 'value' syntax when shredding xml beneficial finding tree structure , populating results. when shredding xml in ssrs report server similar this:

use reportserver go                   (                 select                                  c.name reportname                 ,               cast(sub.extensionsettings xml) xml                 ,               sub.modifieddate subscriptionmodifieddate                 ,               sub.description                 ,               sub.lastruntime                 dbo.reportschedule rs (nolock)                                  join dbo.schedule s (nolock) on rs.scheduleid = s.scheduleid                                 join dbo.subscriptions sub (nolock) on rs.subscriptionid = sub.subscriptionid                                 join dbo.catalog c (nolock) on rs.reportid = c.itemid                 ) select                  a.reportname ,               a.subscriptionmodifieddate ,               a.description ,               t.query('/parametervalues/parametervalue[* = "subject"]/value').value('.', 'varchar(max)') subject     cross apply a.xml.nodes('//parametervalues') n(t)