与 NVARCHAR(MAX) 相比，SQL Server 中 XML 数据类型的性能损失是多少? [英] What is the performance penalty of XML data type in SQL Server when compared to NVARCHAR(MAX)?

问题描述

我有一个要保存日志条目的数据库.

I have a database that is going to keep log entries.

日志表中的一列包含序列化(到 XML)的对象，我团队中的一个人建议使用 XML 数据类型而不是 NVARCHAR(MAX).此表将永远"保留日志(将来可能会考虑归档一些非常旧的条目).

One of the columns in the log table contains serialized (to XML) objects and a guy on my team proposed to go with XML data type rather than NVARCHAR(MAX). This table will have logs kept "forever" (archiving some very old entries may be considered in the future).

我有点担心 CPU 开销，但我更担心 DB 可以增长得更快(参考问题中的 FoxyBOA 在使用 XML 时获得了 70% 的更大的 DB).

I'm a little worried about the CPU overhead, but I'm even more worried that DB can grow faster (FoxyBOA from the referenced question got 70% bigger DB when using XML).

我已阅读此问题 它给了我一些想法，但我对澄清数据库大小是增加还是减少特别感兴趣.

I have read this question and it gave me some ideas but I am particularly interested in clarification on whether the database size increases or decreases.

能否请您分享您在这方面的见解/经验.

Can you please share your insight/experiences in that matter.

顺便说一句.我目前不需要依赖 SQL Server 中的 XML 功能(在特定情况下对我来说优势几乎为零).偶尔会提取日志条目，但我更喜欢使用 .NET 处理 XML(通过编写小型客户端或使用 .NET 程序集中定义的函数).

BTW. I don't currently have any need to depend on XML features within SQL Server (there's nearly zero advantage to me in the specific case). Ocasionally log entries will be extracted, but I prefer to handle the XML using .NET (either by writing a small client or using a function defined in a .NET assembly).

推荐答案

如果您确实有 XML，并且可以确定它始终是 XML，我肯定会建议您采用这种方式.SQL Server 以优化的格式存储 XML - 您甚至不需要任何 XML 索引即可从中受益.

If you do have XML, and you can be sure it's always XML, I would definitely recommend going that way. SQL Server stores XML in an optimized format - you don't even need any XML indices on that to benefit from it.

如果将 5KB XML 的 5000 行插入到 XML 列中，您将获得大约 1250 页 = 9 MB.将具有相同 5KB XML 的相同 5000 行插入 NVARCHAR(MAX) 使用超过 3700 页或 29 MB - 非常不同！

If you insert 5000 rows of a 5KB XML into an XML column, you get roughly 1250 pages = 9 MB. Inserting the same 5000 rows with the same 5KB XML into NVARCHAR(MAX) uses over 3700 pages or 29 MB - quite a difference!

如果您可以将您的 XML 与存储在 SQL Server 中的 XML 架构相关联，那么这种差异应该会更加明显.此外，您还可以保证存储的 XML 符合模式 - 有时会非常有帮助！不能用普通的 NVARCHAR(MAX) 列做到这一点...

And that difference should be even more pronounced if you can associate your XML with a XML schema stored in SQL Server. Plus you're also guaranteed that the XML stored conforms to a schema - can be very helpful at times! Can't do that with a plain NVARCHAR(MAX) column...

而且我不同意在 NVARCHAR(MAX) 上使用 XML 有任何性能损失 - 恰恰相反.由于您在准备显示或获取内容时从 SQL Server 检索的数据可能更少，因此我认为它甚至比 NVARCHAR(MAX) 快一点.

And I don't agree that using XML over NVARCHAR(MAX) has any performance penalty - quite the contrary. Since you're potentially retrieving less data from SQL Server when you're about to display or fetch the content, I would argue it's even a tad faster than NVARCHAR(MAX).

这篇关于与 NVARCHAR(MAX) 相比，SQL Server 中 XML 数据类型的性能损失是多少?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！