Which inserts faster, XML field or Varchar(max) field?

Question

We have a history table that stores xml web service requests and responses. Currently it stores them into an XML field, but we're having performance issues with inserts. We only insert records, no updates, selects or deletes. We've truncated the table and rebuilt the index, to no avail. The table has a Primary clustered index on the identity field, and a default value, GetDate(), on a datetime field. We're running SQL 2005 Server, but the database is in SQL 2000 compatibility mode.

If we change the field type from XML to VarChar(max) or VarChar(xxx), would this speed up the inserts? Is there anything else we should be looking at?

Thanks.

Answer 1

varchar is faster - no parsing needed. XML data type does some pretty heavy internal lifting.

Answer 2

It depends what the performance problem is.

• If is CPU bound, then the XML validation could be a factor and varchar(max) could be faster.
• If is IO bound then the XML compressed storage is better than the nvarchar(max) loose format and you'd loose performance.
• On the other hand, if the problem XML fragments are small the varchar storage presents the opportunity for in-row storage and may bet better than XML
• On yet another hand the in-row storage would decrease the leaf pages row density and cause performance problems for reads.
• If the problem is neither CPU nor IO but is lock contention, then the XML vs. varchar issue is orthogonal to the performance problem. Same goes for insert hot spot page latch contention problem. And again, same goes for log flush performance problem. All would manifest as 'slow inserts' (for some definition of slow) and none would change in the slightest by replacing an XML with varchar.

So, as with all performance problems, the recommendation is to measure first and cut later. The recommended approach is to apply a well tested and proven performance investigation methodology, like Waits and Queues. Guessing will land you nowhere fast.