Avoiding OutOfMemoryExceptions when using StringBuilder.ToString to send files


My customers like to run massive reports, and have my application provide them as a file download.  The DataTables that are returned from SQL Server can be anything up to 300,000 rows, which once HTML is added for formatting, can be up to 100MB.

StringBuilder.ToString causes OutOfMemoryExceptions

Previously my code iterated through a DataTable, and threw together a CSV file (this is simplified a little for legibility):

' quick and dirty CSV file from DataTable '
Dim osb As New StringBuilder
    For Each dc As DataColumn In dt.Columns
    For Each dr As DataRow In dt.Rows
        For Each dc As DataColumn In dt.Columns
End If
Dim xls As String = osb.ToString()
Dim r As HttpResponse = HttpContext.Current.Response
r.AddHeader("content-length", System.Text.Encoding.UTF8.GetByteCount(xls).ToString)

However, this line frequently crashed my app:

Dim xls As String = osb.ToString()

The result:

Exception type: OutOfMemoryException
Exception message: Exception of type 'System.OutOfMemoryException' was thrown.
at System.Text.StringBuilder.ToString()
at System.IO.StringWriter.ToString()

With 16GB of RAM, I could not figure out why a 100MB text file would crash the app with an OutOfMemoryException.  However, after tons of reading it turns out that system RAM is not related to this error.  The problem is mixture of how the StringBuilder allocates memory, the garbage collector, me ending the response, and the overall suitability of StringBuilder to meet this challenge.

The hidden downside with the StringBuilder is that it is just far too easy to use!  However, its not suitable for creating large text outputs of this size.

Cue more reading, and a really simple alternative...

Avoid StringBuilder by Using StreamWriter

The general consensus online is to use StreamWriter and write the data to a file.  StreamWriter streams data, meaning that it uses far less memory than writing a buffered response, so its more efficient.  Because the operating system has far more hard drive space than the StringBuilder has memory, we can generate enormous files with ease.

StreamWriter Gotchas

There are two small downsides however.  One is that you can't modify the data after its written, like you can with a StringBuilder/String.  Also, you end up with a file that may need deleting after it has been sent to the user.  Either way, these aren't really a problem.

Sending Large DataTables to Users with StreamWriter

' delete any files that are more than a day old '
Dim exportDir As String = "D:\myapps\myapp\tmpdir"
For Each fi As FileInfo In New DirectoryInfo(exportDir).GetFiles()
    If (DateTime.Now - fi.CreationTime).TotalHours > 24 Then
    End If

' stream text to file '
If Not Directory.Exists(exportDir) Then
End If

' generate unique file name, e.g. MyFile_20150208152354.csv '
Dim d As DateTime = DateTime.Now() ' date stamp '
Dim dateStamp as String = String.Format("{0}{1}{2}{3}{4}{5}", d.Year.ToString, d.Month.ToString("00"), d.Day.ToString("00"), d.Hour.ToString("00"), d.Minute.ToString("00"), d.Second.ToString("00"))
Dim exportFileName As String = String.Format("MyFile_{0}.csv", dateStamp) 
Dim physicalExportFilePath As String = Path.Combine(exportDir, exportFileName)

' write to temporary file (which is created automatically) '
Using sw As New StreamWriter(physicalExportFilePath, False, Encoding.UTF8)
    For Each dc As DataColumn In dt.Columns
        sw.Write(String.Concat(dc.ColumnName, ","))
    For Each dr As DataRow In dt.Rows
            For Each dc As DataColumn In dt.Columns
            sw.Write(String.Concat(dr.Item(dc.ColumnName).ToString, ","))
End Using

Dim r As HttpResponse = HttpContext.Current.Response
r.AddHeader("content-disposition", String.Format("attachment; filename={0}.xls;", exportFileName))
r.AddHeader("Content-Length", New IO.FileInfo(physicalExportFilePath).Length.ToString())

Testing this out produced some very impressive results.  Not only is streaming to a file really fast, it can generate huge files that the StringBuilder was previously unable to achieve.


No comments have been posted. Be the first to write a comment...

New Comment

This is to prove that you are not a spam robot posting links to dodgy sites!