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:
我正在将客户端的SSIS包从DTS转换为SSIS。在他们的一个包中,他们有一个执行SQL任务,其查询类似于:
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.
此查询似乎在旧系统上返回了相当长的时间,但在新框中,在SSMS中运行查询最多需要18分钟。有时如果我运行它会生成一个XML链接,如果我点击它来查看XML,它会抛出一个'System.OutOfMemoryException',并建议增加从服务器检索的XML数据字符数。我将选项增加到无限制但仍然出错。
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!
该表本身包含220,500行,但返回的查询行在查询停止之前显示129,810。这只是系统没有足够的内存吗?这个盒子有48 GB(Win 2008 R2 EE x64),实例上限为18GB,因为它的共享开发环境。任何帮助/见解都会非常感激,因为我真的不懂XML!
3 个解决方案
#1
2
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.
当您使用SSMS对XML进行XML查询时,它将生成所有XML,然后将其放入网格中并允许您单击它。它带回的数据有多少限制,有220,000行,具体取决于表的宽度,是巨大的,并产生大量文本。
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.
内存不足的事实是SQL Server正在尝试解析所有这些内容,这就是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).
您可以尝试执行文件并查看大小。但是内存耗尽的主要原因是因为这是大量的XML并将其返回到网格中,所以使用这种类型的结果集(大小明智)不会得到所有结果。
DBADuck (Ben)
#2
1
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.
您遇到的内存不足异常是由.net网格控件可以处理的文本量造成的。 220k线是巨大的! SSMS中显示无限数据的设置仅与.net控制内存上限一样好。
#3
0
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.
您可以查看删除ELEMENTS选项并查看属性格式的数据。这将减少返回的XML“字符串空间”的数量。就个人而言,仅凭这个原因,我更喜欢属性而不是元素。上下文是王道,所以它取决于你想要完成的事情(查看数据或使用数据)。你可以将数据传输到XML变量吗?当所有的事情都说完了,DBADuck在他的陈述中100%正确。
SqlNightOwl
#1
2
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.
当您使用SSMS对XML进行XML查询时,它将生成所有XML,然后将其放入网格中并允许您单击它。它带回的数据有多少限制,有220,000行,具体取决于表的宽度,是巨大的,并产生大量文本。
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.
内存不足的事实是SQL Server正在尝试解析所有这些内容,这就是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).
您可以尝试执行文件并查看大小。但是内存耗尽的主要原因是因为这是大量的XML并将其返回到网格中,所以使用这种类型的结果集(大小明智)不会得到所有结果。
DBADuck (Ben)
#2
1
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.
您遇到的内存不足异常是由.net网格控件可以处理的文本量造成的。 220k线是巨大的! SSMS中显示无限数据的设置仅与.net控制内存上限一样好。
#3
0
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.
您可以查看删除ELEMENTS选项并查看属性格式的数据。这将减少返回的XML“字符串空间”的数量。就个人而言,仅凭这个原因,我更喜欢属性而不是元素。上下文是王道,所以它取决于你想要完成的事情(查看数据或使用数据)。你可以将数据传输到XML变量吗?当所有的事情都说完了,DBADuck在他的陈述中100%正确。
SqlNightOwl