Xml retrieving is slower after adding xml index, any idea?

I have a table, it has a field call extrafield, it stores xml like

<field name="name1"></field>
<field name="name2"></field>
...

I tested on 30k records, query like

select * from thetable where extrafields.value('(/field[@name="name2"]/.)[1]','int')<3000

takes around 8 seconds to run, but after I created xml index, I tested after each index created(primary, three different seconary on value,path and property), it takes far more than 8 seconds to run the same query, any idea why it does like that

thanks!

Answer this question

Xml retrieving is slower after adding xml index, any idea?

Paul Coddington
To benefit from XML indexes for your range query, you need to do the following:

1) Use typed XML. Looking at your query, the type of <field> can be xs:integer. The benefits of using typed XML for range queries is described under the section "Typed or untyped XML" in the MSDN whitepaper on optimizing XML applications:
http://msdn.microsoft.com/xml/default.aspx pull=/library/en-us/dnsql90/html/sqloptxml.asp

2) Use the exist() method instead of the value() method:
extrafields.exist('/field[@name="name2"][. < 3000]') = 1

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server
Xml retrieving is slower after adding xml index, any idea?

Answer this question