SQL Query FOR XML 在 2000 年运行良好，在 2008 R2 运行缓慢
我正在将客户端的 SSIS 包从 DTS 转换为 SSIS。在他们的一个包中，他们有一个执行 SQL 任务，该任务具有与此类似的查询：
SELECT * FROM [SOME_TABLE] AS ReturnValues
ORDER BY IDNumber
FOR XML AUTO, ELEMENTS
此查询似乎在旧系统上在相当长的时间内返回，但在新系统上运行该查询最多需要 18 分钟SSMS。有时，如果我运行它，它会生成一个 XML 链接，如果我单击它来查看 XML，它会抛出“System.OutOfMemoryException”，并建议增加从服务器检索 XML 数据的字符数。我将选项增加到无限制，但仍然出现错误。
该表本身包含 220,500 行，但在查询停止之前返回的查询行显示为 129,810 行。这仅仅是系统没有足够可用内存的问题吗？这个盒子有 48 GB (Win 2008 R2 EE x64)，实例上限为 18GB，因为它的共享开发环境。任何帮助/见解将不胜感激，因为我真的不了解 XML！
I'm converting a client's SSIS packages from DTS to SSIS. In one of their packages they have an execute SQL task that has a query similar to this:
SELECT * FROM [SOME_TABLE] AS ReturnValues
ORDER BY IDNumber
FOR XML AUTO, ELEMENTS
This query seems to return in a decent amount of time on the old system but on the new box it takes up to 18 minutes to run the query in SSMS. Sometimes if I run it it will generate an XML link and if I click on it to view the XML its throwing a 'System.OutOfMemoryException' and suggests increasing the number of characters retrieved from the server for XML data. I increased the option to unlimited and still getting error.
The table itself contains 220,500 rows but the query rows returned is showing 129,810 before query stops. Is this simply a matter of not having enough memory available to the system? This box has 48 GB (Win 2008 R2 EE x64), instance capped to 18GB because its shared dev environment. Any help/insight would be greatly appreciated as I don't really know XML!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
当您使用 SSMS 对 XML 进行 XML 查询时，它会生成所有 XML，然后将其放入网格中并允许您单击它。它带回的数据量是有限的，220,000 行（具体取决于表的宽度）很大，并且会生成大量文本。
内存不足是因为 SQL Server 正在尝试解析所有数据，这对于 SSMS 来说是大量内存消耗。
您可以尝试执行一个文件并查看获得的大小。但内存不足的主要原因是因为有大量 XML 并将其返回到网格，您将无法始终使用这种类型的结果集（大小方面）获得所有结果。
DBADuck（本）
When you are using SSMS to do XML queries FOR XML, it will generate all the XML and then put it into the grid and allow you to click on it. There are limits to how much data it brings back and with 220,000 rows, depending on how wide the table is, is huge and produces a lot of text.
The out of memory is the fact that SQL Server is trying to parse all of it and that is a lot of memory consumption for SSMS.
You can try to execute to a file and see what you get for size. But the major reason for running out of memory, is because that is a lot of XML and returning it to the grid, you will not get all the results all the time with this type of result set (size wise).
DBADuck (Ben)
您遇到的内存不足异常是由于 .net 网格控件可以处理的文本量造成的。 220k 行是巨大的！ SSMS 中显示无限数据的设置仅与 .net 控制内存上限一样好。
The out of memory exception you're hitting is due to the amount of text a .net grid control can handle. 220k lines is huge! the setting in SSMS to show unlimited data is only as good as the .net control memory cap.
您可以考虑删除 ELEMENTS 选项并查看属性格式的数据。这将减少返回的 XML“字符串空间”量。就我个人而言，仅仅因为这个原因，我更喜欢属性而不是元素。上下文为王，因此这取决于您想要完成的任务（查看数据或使用数据）。您能否将数据通过管道传输到 XML 变量中？当一切都说了&完成后，DBADuck 的说法 100% 正确。
SqlNightOwl
You coul look at removing the ELEMENTS option and look at the data in attribute format. That will decreate the amount XML "string space" returned. Personally, I prefer attributes over elements for that reason alone. Context is king, so it depends on what you're trying to accomplish (look at the data or use the data). Could youp pipe the data into an XML variable? When all is said & done, DBADuck is 100% correct in his statement.
SqlNightOwl