Return to SQL Consulting Home

 
Using SQL Server in the Enterprise
issue 1, number 6 - June 2009



Sent to you by the Microsoft-certified experts at SQL Consulting, Inc.


In This Issue
XML is a useful tool in any database environment, but it has its limits.  Pushing it past its limits can result in serious performance issues.

More SQLTopics
Check out all the SQL Server information that didn't make it into the monthly newsletter.
The SQL Blog

Free SQL Server Advice
A few minutes of free SQL Server advice is a courtesy we extend to all our current and former clients, and in fact, to anyone reading this newsletter.
Sometimes you don’t really need a SQL Server consultant. Sometimes all you need is the answer to a question or two to save yourself hours of searching.
Give us a call when you’re stuck. We will be happy to spend a few minutes answering questions and giving you the best advice we have about your SQL Server issue. There is no charge, no strings attached.

SQL Consulting Home

Call us Toll-free
877.201.3428

The Perils of XML Overuse

Like all technologies, XML has its strong and weak points. It is useful in many situations. However it is often used in places where it is not appropriate. The performance cost of XML mis-use or over-use can be enormous as shown in test results I recently reviewed. 

Test Parameters
The test was a comparison of data retrieval times by relational and xml methods. The data consisted of approximately 100,000 orders and 1.2 million order line items. In the relational model they were stored as an Orders table and an OrderDetail table.  XML was tested separately in element format and in attribute format.  Both relational and xml data had similar indexes.

Below is a table summarizing the results. A cold execution means the query was executed after the buffers were flushed and no data was in cache. A hot execution was done after the data was in cache:

Data FormatCold Exec (ms}Hot Exec (ms)Storage
XML Element5937337054397
XML Attribute319031407
Relational Tables2501214

As you can see there is a HUGE performance degradation using element-style XML. It is on the order of 37,000% slower when hot execution times are compared with relational data retrieval. Attribute style XML fares better but it is still many times less efficient than data retrieval from relational tables.

As well as having performance issues, both forms of xml take almost twice the disk space of relational data as can be seen the Storage column of the table above.

XML has many legitimate uses in a database environment but  manipulation of large datasets is not one of them.
 
Return to SQL Consulting Home