CODEWITHSUNDEEP
sql-serverxmlxquery-sql
Suresh
Suresh

Reputation: 1169

Fast data retrieval from XML type field in MS SQL Server

I have a tbl_Data table in MS SQL Server 2008, which having two fields, ID is primary Key and CustomFields column can have XML Data. 

 [ID] [uniqueidentifier] [CustomFields] [xml] NULL

Average nodes in XML - 25 nodes in each record (some records having null, some having up to 70 nodes) Total Record count in tbl_Data : 80000 rows.

Below is a sample data for CustomFields Column.

<Fields>
<Field Id="StatusName" Value="Approve" />
<Field Id="LogNumber" Value="70470" />
<Field Id="SubmittedDate" Value="Feb  7 2012 12:00AM" />
..
<Fields>

I am trying to execute below query for retrieve records which having LogNumber 70470, This Query taking 1 Minute to generate result.

 SELECT ID FROM [tbl_Data] WHERE
CustomFields.exist('/Fields/Field[@Title = "LogNumber"]') = 1
     AND CustomFields.exist('/Fields/Field[@Value="70470"]') = 1

I have tried to apply XML Index, but seems was not helpful.

Your valuable suggestion or advice is most welcome which you think for improve data retrieval performance i.e. Do I need to change XML structure?

Thanks Suresh

Upvotes: 2

Views: 2895

Answers (1)

Kahn
Kahn

Reputation: 1660

As explained by this response here (link), you can dramatically increase the performance of XML-queries by using both a schema and an index in XML-columns. Basically, create a schema collection applicable to your data, alter the column to that datatype, add the XML-index and profit.

In my case shredding a huge XML file to various forms of data took days by default. An index reduced it to around 10 hours, while using a schema reduced that to 20 seconds, which was more than good enough.

Upvotes: 1

Related Questions