1

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

Suresh
  • 1,131
  • 1
  • 15
  • 28

1 Answers1

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.

Community
  • 1
  • 1
Kahn
  • 1,630
  • 1
  • 13
  • 23
  • 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! – Suresh Feb 06 '14 at 06:02