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.
我们有一个存储xml Web服务请求和响应的历史表。目前它将它们存储到XML字段中，但是我们遇到了插入的性能问题。我们只插入记录，不更新，选择或删除。我们截断了表并重建了索引，但无济于事。该表在标识字段上具有主聚簇索引，在日期时间字段上具有默认值GetDate（）。我们正在运行SQL 2005 Server，但数据库处于SQL 2000兼容模式。
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?
如果我们将字段类型从XML更改为VarChar（max）或VarChar（xxx），是否会加快插入速度？还有什么我们应该看的吗？
Thanks.
谢谢。
2 个解决方案
#1
3
varchar is faster - no parsing needed. XML data type does some pretty heavy internal lifting.
varchar更快 - 无需解析。 XML数据类型做了一些非常繁重的内部提升。
#2
6
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.
- 如果是CPU绑定，则XML验证可能是一个因素，varchar（max）可能更快。
- If is IO bound then the XML compressed storage is better than the nvarchar(max) loose format and you'd loose performance.
- 如果是IO绑定，则XML压缩存储优于nvarchar（max）松散格式，并且您将失去性能。
- 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
- 另一方面，如果问题XML片段很小，varchar存储提供了行内存储的机会，可能比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.
- 如果问题既不是CPU也不是IO，而是锁争用，那么XML与varchar问题就与性能问题正交。插入热点页面锁存争用问题也是如此。同样，对于日志刷新性能问题也是如此。所有这些都会表现为“慢插入”（对于某些慢速定义），并且通过用varchar替换XML，没有任何内容会发生变化。
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.
因此，与所有性能问题一样，建议首先进行测量，然后再进行削减。建议的方法是应用经过良好测试和验证的性能调查方法，如等待和队列。猜测会让你无法快速降落。
#1
3
varchar is faster - no parsing needed. XML data type does some pretty heavy internal lifting.
varchar更快 - 无需解析。 XML数据类型做了一些非常繁重的内部提升。
#2
6
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.
- 如果是CPU绑定，则XML验证可能是一个因素，varchar（max）可能更快。
- If is IO bound then the XML compressed storage is better than the nvarchar(max) loose format and you'd loose performance.
- 如果是IO绑定，则XML压缩存储优于nvarchar（max）松散格式，并且您将失去性能。
- 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
- 另一方面，如果问题XML片段很小，varchar存储提供了行内存储的机会，可能比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.
- 如果问题既不是CPU也不是IO，而是锁争用，那么XML与varchar问题就与性能问题正交。插入热点页面锁存争用问题也是如此。同样，对于日志刷新性能问题也是如此。所有这些都会表现为“慢插入”（对于某些慢速定义），并且通过用varchar替换XML，没有任何内容会发生变化。
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.
因此，与所有性能问题一样，建议首先进行测量，然后再进行削减。建议的方法是应用经过良好测试和验证的性能调查方法，如等待和队列。猜测会让你无法快速降落。