为什么架构会使 XML 列变慢？
我有一个带有 XML 列的表。 这些文档非常大且数量众多，我一直在尝试各种方法来提高基本查询的性能。
因为文档表明将 XML 架构应用于列可以帮助 SQL Server 优化查询（并且因为有一个可用），所以我创建了一个架构集合并将该类型应用于我的 XML 列。
查询性能急剧下降。
我的意思是，最简单的测试变得慢得难以忍受。 我删除了列类型，查询执行得和以前一样好。 这有发生在其他人身上吗？
我应该指出，该模式本身相当庞大且复杂。 尽管如此，这个结果对我来说似乎违反直觉。
编辑：我只关心 SELECT 语句——事实上，这些文档永远不会被编辑，只会被浏览。 因此，一旦插入并验证了记录，我不明白为什么除了可能的优化之外还需要该模式。
另外，我知道此问题，其中 index 会对性能产生负面影响。 这不是我的问题——索引对我正在运行的查询没有明显的影响，无论是积极的还是消极的，并且我已经尝试了使用和不使用索引的上述场景。
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.
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.
Query performance went off a cliff.
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.
EDIT: 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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我想你心里已经有了答案：
复杂的模式可能需要大量计算。 如果您在更新方面做了很多工作，则必须多次验证架构。
I think you've got your answer right there:
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.