Issues with performance of XQuery on SQL Server 2005

Hi folks,

we are executing the following Xquery on SQLserver 2005.

select
policy_xml.query('/Policy/PolicyApplication/Inuserer/InsurerID'),
policy_xml.query('/Policy/PolicyApplication/Insurer/AccountIdentifier'),
policy_xml.query('/Policy/PolicyApplication/Insurer/Type'),
policy_xml.query('/Policy/PolicyApplication/Insurer/HolderName')
from policyTable
where
policy_xml.exist('/Policy/PolicyApplication/Insurer/PolicyOwner/EntityID[.="E_1"]') = 1

Its taking 50 Secs to search from 10000 records{without indexes}

The table has 3 columns sno,policy_id,policy_xml.

We have primary index on policy_id field and 1 secondary index(path index) on the table.
When we enable the index the query takes 380 secs.

The size of loan_xml column is about 110 Kb for each row. We need to keep the indexes for some more complex update Xqueries. Is there a way out to improve the performance of the XQuery we are using? Please let us also know the reasons of decrease in performance using indexes on the table. Do indexs have any issues related to XQuery performance?

We have an urgent requirement to resolve this issue. Kindly let us know the resolution ASAP.

Please let us know if you require any other information in this regard.

Thanks,

Bhuvanesh

I am not a XML guru to exactly know where the problem might be but want to pass the following link

which talks of some performance techiniques.

http://msdn2.microsoft.com/en-us/library/ms345118(SQL.90).aspx

Regards

AK

|||

Hi Bhuvanesh,

From what you said here:

The table has 3 columns sno,policy_id,policy_xml.

We have primary index on policy_id field and 1 secondary index(path index) on the table.

I assume you haven't create XML index on column policy_xml, the DDL statement will look like this

Code Snippet

createprimaryxmlindex p_xml_idx

on policyTable(policy_xml)

go

It should definitely improve your query performance. Please let me know if otherwise.

|||

I've similar issue.

Table1 ( Xid , XML_data (xml)) where Xid is primary key and XML_data is of xml datatype.

I've set primary index on XML_data along with the three secondary indexes.

with fillfactor 90, padindex on and sort _in_tempdb is on.

xml_data stores 1000 xml's of size 150 kb each. i'm fetching single xml for particular value and it is taking 20 minutes on server with 2 gb ram.

the query is

SELECT
xml_data.query('/Info/PersonalInfo/Entity[1]/LastName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[1]/FirstName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[1]/Sno/text()'),

xml_data.query('/Info/PersonalInfo/Entity[2]/LastName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[2]/FirstName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[2]/Sno/text()')
FROM Table1

Looking out for valuable help.....

|||

Try following query to see if any improvement. I change query() to value() since you seems want to get scalar value, not xml.

Code Snippet

SELECT

x.value('(.[1]/LastName)[1]','varchar(100)'),

x.value('(.[1]/FirstName)[1]','varchar(100)'),

x.value('(.[1]/Sno)[1]','varchar(100)'),

x.value('(.[2]/LastName)[1]','varchar(100)'),

x.value('(.[2]/FirstName)[1]','varchar(100)'),

x.value('(.[2]/Sno)[1]','varchar(100)')

FROM Table1 crossapply xml_data.nodes('/Info/PersonalInfo/Entity')as t(x)