Fast data retrieval from XML type field in MS SQL Server

Question

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

Community · Accepted Answer · 2017-05-23 12:08:13Z

1

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.

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Jan 28, 2014 at 9:48

Kahn

1,6601 gold badge13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Suresh Over a year ago

Thanks for your answer! I applied Full Text Index on XML & It solved Performance issue, now able to query data using wildcard search and it working fast!

Collectives™ on Stack Overflow

Fast data retrieval from XML type field in MS SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related