为什么模式会使 XML 列变慢? [英] Why would a schema make an XML column slower?

问题描述

我有一个带有 XML 列的表.文档很大而且数量很多，我一直在尝试各种方法来提高基本查询的性能.

I have a table with an XML column. The documents are pretty large and numerous, and I've been trying various ways to improve the performance of basic queries.

因为文档表明将 XML 架构应用于列可以帮助 SQL Server 优化查询——而且因为有一个可用——我创建了一个架构集合并将该类型应用于我的 XML 列.

Because the documentation indicates that applying an XML Schema to the column can help SQL Server to optimize queries — and because one was available — I created a schema collection and applied the type to my XML column.

查询性能一落千丈.

我的意思是，最简单的测试变得非常缓慢.我删除了列类型，查询执行得和以前一样好.其他人也遇到过这种情况吗?

I mean, the very simplest tests became intolerably slow. I removed the column type, the queries performed just as well as before. Has this happened to anyone else?

我应该注意到模式本身相当大且复杂.不过，这个结果对我来说似乎违反直觉.

I should note that the schema itself is rather large and elaborate. Still, this result seems counter-intuitive to me.

我只关心 SELECT 语句——事实上，这些文档永远不会被编辑，只会被浏览.所以我不明白为什么除了可能的优化之外的任何事情都需要架构，一旦记录被插入和验证.

My concern is with SELECT statements only -- in fact, these documents will never be edited, only browsed. So I can't see why the schema would be needed for anything besides possible optimizations, once the records have been inserted and validated.

另外，我知道这个问题，其中XML 列上的索引会对性能产生不利影响.这不是我的问题 - 索引对我正在运行的查询没有明显的影响，无论是正面还是负面，而且我已经尝试了使用和不使用索引的上述场景.

Also, I'm aware of this issue, where an index on an XML column can adversely affect performance. That's not my problem — an index has had no noticeable impact, positive or negative, on the queries I'm running, and I have tried the above scenario with and without the index.

推荐答案

我想你已经有了答案:

我应该注意到模式本身相当大和复杂

复杂的模式可能需要大量计算.如果您在更新方面做得特别多，则必须多次验证架构.

A complicated schema can be computationally intense. If you're doing much in the way of updates especially, the schema has to be validated multiple times.

这篇关于为什么模式会使 XML 列变慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！