Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with 21 columns. The majority are numerical and the rest are nvarchar.

I need to add 4 columns that will contain XML data for each row. The XML data for each row can be anywhere from 200 to 2,000 lines.

The question is:

  • Does adding the XML typed columns to the same table change the speed of querying that table?

  • What are the performance benefits when I add the xml typed columns to another table and join both tables when doing queries?

  • Is it better to encode the XML data to shrink it and decode it in the application?

share|improve this question
add comment

migrated from stackoverflow.com Mar 1 '13 at 20:14

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote -1 down vote accepted

Increasing the size of a table will always have a negative impact on performance. So adding XML columns will have a negative impact.

Splitting the tables will result in more flexibility as you will not always shuffle between the xmls.

That depends on your need to browse and use the xmls on the server side. SQL does not keep the xml in the database as a string (nvarchar) but in an optimized form. If you do not need to use it as it is in tsql then you will not need to keep it as an XML datatype so that means you can compress it as you want and keep it even binary.

share|improve this answer
add comment

There are a lot of facets to this, so it really depends.

Does adding the XML typed columns to the same table change the speed of querying that table?

More data potentially could be read (I/O), but when you say speed of querying, if you mean finding rows based on columns outside of the XML, this should usually be based on a index and so the xml should not be especially involved in any performance differences

xml columns can be stored in row or out of row, with obvious implications for number of rows which can be stored in a page, fragmentation etc... http://technet.microsoft.com/en-us/library/ms189087(v=sql.105).aspx Storing out of row should mean that more rows fit per page, so less data is read and discarded when you don't actually have the xml data stored in-row.

What are the performance benefits when I add the xml typed columns to another table and join both tables when doing queries?

Well, this is basically equivalent to storing out of row. You'll only get an administrative benefit doing it yourself in terms of being able to manage the other table independently.

Is it better to encode the XML data to shrink it and decode it in the application?

Perhaps. If we're assuming that you don't need to query it in the database (xml data type with xml query techniques), then why stored it as xml at all instead of varbinary or varchar?

share|improve this answer
add comment
  1. Yes!
  2. Better speed, and greater control of resources.
  3. That trades-off resources on SQL server against resources in the application. Which has more excess capacity?
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.