从SqlDataReader读取字符串时内存不足 [英] Out of Memory when reading a string from SqlDataReader

问题描述

我遇到了我不知道的最奇怪的事情.我有一个SQL表，在ntext字段中存储了一堆报告.当我将其中一个的值复制并粘贴到记事本中并将其保存(用于Visual Studio以便从其他行中的较小报表中获取值)时，原始txt文件约为5Mb.当我尝试使用SqlDataReader获取相同的数据并将其转换为字符串时，出现内存不足异常.这是我尝试执行的操作:

I'm running into the strangest thing that I can't figure out. I have a SQL table with a bunch of reports stored in an ntext field. When I copied and pasted the value of one of them into notepad and saved it (used Visual Studio to grab the value from a smaller report in a differente row), the raw txt file was about 5Mb. When I try to get this same data using SqlDataReader and convert it to a string, I get an out of memory exception. Here is how I am trying to do it:

string output = "";
string cmdtext = "SELECT ReportData FROM Reporting_Compiled WHERE CompiledReportTimeID = @CompiledReportTimeID";
SqlCommand cmd = new SqlCommand(cmdtext, conn);
cmd.Parameters.Add(new SqlParameter("CompiledReportTimeID", CompiledReportTimeID));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    output = reader.GetString(0); // <--- exception happens here
}
reader.Close();

我尝试创建一个对象和一个stringbuilder来获取数据，但是仍然遇到相同的内存不足异常.我也尝试过使用reader.GetValue(0).ToString()也是徒劳的.该查询仅返回1行，而当我在SQL Management Studio中运行该查询时，它会尽可能地令人满意.

I tried creating an object and a stringbuilder to grab the data, but I still get the same out of memory exception. I've also tried using reader.GetValue(0).ToString() as well to no avail. The query only returns 1 row, and when I run it in SQL Management Studio its as happy as can be.

抛出的异常是:

System.OutOfMemoryException was unhandled by user code  
Message=Exception of type 'System.OutOfMemoryException' was thrown.  
Source=mscorlib  
 StackTrace:  
 at System.String.CreateStringFromEncoding(Byte* bytes, Int32 byteLength, Encoding       encoding)  
   at System.Text.UnicodeEncoding.GetString(Byte[] bytes, Int32 index, Int32 count)  
   at System.Data.SqlClient.TdsParserStateObject.ReadString(Int32 length)  
   at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)  
   at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)  
   at System.Data.SqlClient.SqlDataReader.ReadColumnData()  
   at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)  
   at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)  
   at Reporting.Web.Services.InventoryService.GetPrecompiledReportingData(DateTime ReportTime, String ReportType) in   C:\Projects\Reporting\Reporting.Web\Services\InventoryService.svc.cs:line 3244  
   at SyncInvokeGetPrecompiledReportingData(Object , Object[] , Object[] )  
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)  
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)  
 InnerException:   
    null

我用其他行号进行了测试，但是行号正确，因为那些测试ID没有数据.在查看包含几乎相同的报告的表后，我提取了其他一些测试ID，并且得到了相同的异常.也许它的字符串如何编码?表中存储的数据是JSON编码的字符串，该字符串是由我在其他地方创建的一个非常粗糙的类生成的，以防万一.

I had tested with other row numbers that appeared to work, but that was a false positive as those test ID's had no data. I pulled some other test ID's after looking at the table that contain reports that are near identical, and I get the same exception. Maybe its how the string is encoded? The data stored in the table is a JSON encoded string that was generated out of a really gnarly class I made somewhere else, in case that helps.

这是前面的代码块:

// get the report time ID
int CompiledReportTimeTypeID = CompiledReportTypeIDs[ReportType];
int CompiledReportTimeID = -1;
cmdtext = "SELECT CompiledReportTimeID FROM Reporting_CompiledReportTime WHERE CompiledReportTimeTypeID = @CompiledReportTimeTypeID AND CompiledReportTime = @ReportTime";
cmd = new SqlCommand(cmdtext, conn);
cmd.Parameters.Add(new SqlParameter("CompiledReportTimeTypeID", CompiledReportTimeTypeID));
cmd.Parameters.Add(new SqlParameter("ReportTime", ReportTime));
reader = cmd.ExecuteReader();
while (reader.Read())
{
    CompiledReportTimeID = Convert.ToInt32(reader.GetValue(0));
}
reader.Close();

CompiledReportTypeIDs是一个字典，该字典根据在方法开始时输入的字符串参数来获取正确的CompiledReportTimeTypeID. ReportTime是一个较早输入的DateTime.

CompiledReportTypeIDs is a dictionary that gets the correct CompiledReportTimeTypeID based on a string parameter that's fed in at the beginning of the method. ReportTime is a DateTime that is fed in earlier.

我将删除该表并使用ReportData字段将其重新创建为nvarchar(MAX)而不是ntext，以排除SQL数据类型问题.这是一个漫长的过程，我将使用发现的内容再次进行更新.

I am going to drop the table and recreate it with the ReportData field as nvarchar(MAX) instead of ntext, just to rule out a SQL data type issue. It's a long shot and I'll update again with what I find.

Edit2: 将表中的字段更改为nvarchar(max)无效.我也尝试使用output = cmd.ExecuteScalar().ToString()，也没有影响.我正在尝试查看SqlDataReader是否有最大大小.当我从SQL Mgmt Studio复制文本的值时，将其保存在记事本中时只有43Kb.为了验证这一点，我提取了一个具有已知工作ID的报告(一个较小的报告)，当我直接从Visual Studio复制该值并将其转储到记事本中时，它的大小约为5MB！这意味着这些大报告可能位于nvarchar(max)字段中，约为20MB.

Changing the field in the table to nvarchar(max) had no effect. I also tried using output = cmd.ExecuteScalar().ToString() as well, with no impact. I'm trying to see if there is a max size for SqlDataReader. When I copied the value of the text from SQL Mgmt Studio, it was only 43Kb when saved in notepad. To verify this, I pulled a report with a known working ID (a smaller report), and when I copied the value straight out of Visual Studio and dumped it in notepad it was around 5MB! That means these big reports are probably in the ~20MB range sitting in a nvarchar(max) field.

Edit3: 我重新启动了所有内容，以包括开发IIS服务器，SQL服务器和开发笔记本电脑.现在它似乎正在工作.尽管这不是为什么发生的答案.我将开放此问题以解释发生了什么，我将其中之一标记为答案.

I rebooted everything, to include my dev IIS server, the SQL server, and my dev laptop. Now it seems to be working. This isn't the answer as to why this happened though. I'm leaving this question open for explanations as to what happened, and I'll mark one of those as an answer.

Edit4: 话虽如此，我在不做任何更改的情况下又进行了一次测试，并且返回了相同的异常.我真的开始认为这是一个SQL问题.我正在更新此问题上的标签.我制作了一个单独的应用，该应用运行完全相同的查询，并且运行良好.

Having said that, I ran another test without changing a thing and the same exception has returned. I'm really starting to think that this is a SQL issue. I'm updating the tags on this question. I made a separate app that runs the exact same query and it runs fine.

Edit5: 我已按照以下答案之一实施了顺序访问.一切都正确地读入了流中，但是当我尝试将其写成字符串时，我仍然遇到内存不足的异常.这是否表示获取连续的内存块的问题?这是我实现缓冲的方法:

I have implemented sequential access as per one of the answers below. Everything gets read into a stream properly, but when I try to write it out to a string I'm still getting the out of memory exception. Would this indicate the issue of getting a contiguous block of memory? Here is how I implemented the buffering:

                reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
            long startIndex = 0;
            long retval = 0;
            int bufferSize = 100;
            byte[] buffer = new byte[bufferSize];
            MemoryStream stream = new MemoryStream();
            BinaryWriter writer = new BinaryWriter(stream);
            while (reader.Read())
            {
                // Reset the starting byte for the new CLOB.
                startIndex = 0;

                // Read bytes into buffer[] and retain the number of bytes returned.
                retval = reader.GetBytes(0, startIndex, buffer, 0, bufferSize);

                // Continue while there are bytes beyond the size of the buffer.
                while (retval == bufferSize)
                {
                    writer.Write(buffer);
                    writer.Flush();

                    // Reposition start index to end of last buffer and fill buffer.
                    startIndex += bufferSize;
                    retval = reader.GetBytes(0, startIndex, buffer, 0, bufferSize);
                }

                //output = reader.GetString(0);
            }
            reader.Close();
            stream.Position = 0L;
            StreamReader sr = new StreamReader(stream);
            output = sr.ReadToEnd(); <---- Exception happens here
            //output = new string(buffer);

Edit6: 除此之外，当OOM异常发生时，我看到IIS工作进程(包含正在运行的方法)达到了将近700MB.它运行在IIS Express上，而不是生产服务器上的完整IIS.这和它有关系吗?同样，当我调用Byte [] data = stream.ToArray()时，我也会间歇性地获得OOM.我认为我真正需要的是为该过程提供更多内存的方法，但是我不知道在哪里配置它.

To add to this, when OOM exception happens I see the IIS worker process (which holds the method that is running) hit almost 700MB. This is running on IIS Express and not the full IIS on the production server. Would this have anything to do with it? Also when I call Byte[] data = stream.ToArray() I intermittently get the OOM as well. I think what I really need is a way to give more memory to this process, but I don't know where to configure this.

Edit7: 我只是将开发服务器从使用本地计算机上的IIS Express更改为内置的Visual Studio Web服务器. OOM异常现在消失了.我真的认为这是分配连续内存块的问题，无论出于何种原因，IIS Express都不会将其分叉.现在它运行良好，我将在运行常规IIS7的2008R2上发布到功能完善的服务器，以了解其运行情况.

I just changed my dev server from using IIS Express on my local machine to the built-in Visual Studio web server. The OOM exception is now gone. I really think it was the allocating a contiguous block of memory issue, and for whatever reason IIS Express wouldn't fork it over. Now that it is running fine, I will publish to my full blown server on 2008R2 running the regular IIS7 to see how it goes.

推荐答案

您应该尝试通过指定

You should try to read the data sequentially by specifying the command behavior when you execute the reader. Per the documentation, Use SequentialAccess to retrieve large values and binary data. Otherwise, an OutOfMemoryException might occur and the connection will be closed.

虽然顺序访问通常用于大型二进制数据，但根据MSDN文档，您也可以使用它来读取大量字符数据.

While sequential access is typically used on large binary data, based on the MSDN documentation you can use it to read large amounts of character data as well.

访问BLOB字段中的数据时，请使用GetBytes或 数据读取器的GetChars类型的访问器，该访问器用 数据.您也可以将GetString用于字符数据.然而.到 节省系统资源，您可能不想加载整个BLOB 值转换为单个字符串变量.您可以改为指定一个 要返回的数据的特定缓冲区大小以及起始位置 从返回的数据中读取的第一个字节或字符. GetBytes和GetChars将返回一个长值，该值表示 返回的字节数或字符数.如果将null数组传递给 GetBytes或GetChars，返回的long值将是总数 BLOB中的字节或字符数.您可以选择指定一个 数组中的索引，作为要读取的数据的起始位置.

When accessing the data in the BLOB field, use the GetBytes or GetChars typed accessors of the DataReader, which fill an array with data. You can also use GetString for character data; however. to conserve system resources you might not want to load an entire BLOB value into a single string variable. You can instead specify a specific buffer size of data to be returned, and a starting location for the first byte or character to be read from the returned data. GetBytes and GetChars will return a long value, which represents the number of bytes or characters returned. If you pass a null array to GetBytes or GetChars, the long value returned will be the total number of bytes or characters in the BLOB. You can optionally specify an index in the array as a starting position for the data being read.

此 MSDN示例显示了如何执行顺序访问.我相信您可以使用 GetChars 方法阅读文本数据.

This MSDN example shows how to perform sequential access. I believe you can use the GetChars method to read the textual data.

这篇关于从SqlDataReader读取字符串时内存不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！