MSSQL Architecture & Design: Improving Report Performance XML Columns

Techies, Gurus and Wizards--
One of my colleagues has been working with a 3rd party product which stores  xml data in an xml defined column. As this 3rd party product continues to accrue more and more data, the reports which run off this data have gone from 10 minutes to nearly 2 hours.  I've agreed to look at this with him this week. I'm not sure what I'll find, but I suspect whatever the search criteria is for the where clauses filtering these reports will be dependent upon isolating string values found against specific nodes stored within the xml docs in the xml column.

For those of you who've encountered similar issues, how did you approach isolating the problem? If I do discover that values within the xml doc are used to filter data for the reports what approach do I take?

In reading the MS literature on XML data types, I see that there are primary and secondary indexes available. I'm a little confused on the clustered XML primary. Does this mean that if a table already has something like a primary, clustered integer identifier that it is still possible to create an XML primary, clustered key in addition or not?

In any case, I will be grateful for your suggestions on how to apply best practices to resolving poor performance issues against XML data stored in SQL Server 2005!:-)
Paula DiTalloIntegration developerAsked:
Who is Participating?
 
Kobe_LenjouCommented:
To answer your question: Yes. XML indexes are not directly related to 'classic' indexes (however a classic clustered is a prerequisite). This is infact quite logical since XML fields are kind of 'subrecordsets' embedded in a table.

Just add the right XML indexes and it will work fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.