We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

MSSQL Architecture & Design: Improving Report Performance XML Columns

Medium Priority
370 Views
Last Modified: 2012-05-11
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!:-)
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.