[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


MSSQL Architecture & Design: Improving Report Performance XML Columns

Posted on 2011-04-19
Medium Priority
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!:-)
Question by:Paula DiTallo
1 Comment

Accepted Solution

Kobe_Lenjou earned 2000 total points
ID: 35430621
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.

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question