Out of memory exception when pulling huge data from DB

Question

We are pulling a huge data from sql server DB. It has around 25000 rows with 2500 columns. The requirement is to read the data and export it to spread sheet, so pagination is not a choice. When the records are less it is able to pull the data but when it grows to the size i mentioned above it is throwing exception.

public DataSet Exportexcel(string Username)
{
    Database db = DatabaseFactory.CreateDatabase(Config);
    DbCommand dbCommand = 
        db.GetStoredProcCommand("Sp_ExportADExcel");
    db.AddInParameter(dbCommand, "@Username", DbType.String, 
        Username);
    return db.ExecuteDataSet(dbCommand);
}

Please help me in resolving this issue.

can you calculate the dataset size by extracting only 1000 rows and checking size of returned DataSet? multiply then by 25 and check your server has enough memory. beware ASP.NET, IIS, Windows Server architecture might have some limits so if you hit the wall of any of those limits it does not help even if the server has 2 TB of RAM... but first check the actual numbers in your case... — Davide Piras, Feb 07 '12 at 11:52
Why don't you write a query to limit number of rows you fetch from database. Repeat it until you export all of them. — onatm, Feb 07 '12 at 11:53
You need to stream the data, you are exporting it to an offline DataSet, use DataReader to iterate through each row in the record set, that way you won't swallow up the memory in one go. — Mantorok, Feb 07 '12 at 11:53
please have a look at this question which might help you **http://stackoverflow.com/questions/1662982/why-am-i-getting-an-out-of-memory-error-doing-asp-net-excel-interop** — huMpty duMpty, Feb 07 '12 at 11:54
Prevent getting the data as an `DataTable`. Instead stream the results from the database and directly to the browser. — Steven, Feb 07 '12 at 12:20

score 4 · Accepted Answer · answered Feb 07 '12 at 11:52

4

The requirement is to read the data and export it to spread sheet, so pagination is not a choice.

Why not read data in steps. Instead of getting all records at once why not get limited number of records every time and write them to excel. Continue until you have processed all the records

answered Feb 07 '12 at 11:52

Haris Hasan

29,856
10
92
122

that is the last option i have, because in this case i need to parse the results and write one by one to excel. Now i am directly binding it to the gridview and rendering it to spreadsheet – Jeeva Feb 07 '12 at 11:55
So you want the easy way :) you should mention this in question. – Renatas M. Feb 07 '12 at 11:58
It doesn't make sense to show 25000 records in a GridView at once in my opinion. I have a feeling you are doing it just for the sake of export to excel. – Haris Hasan Feb 07 '12 at 12:00

score 1 · Answer 2 · answered Feb 07 '12 at 12:00

Your problem is purely down to the fact that you are trying to extract so much data in one go. You may get around the problem by installing more memory in the machine doing the query, but this is just a bodge.

Your best to retrieve such amounts of data in steps.

You could quite easily read the data back row by row and export/append that in CSV format to a file and this could all be done in a stored procedure.

You don't say what database you are using, but handling such large amounts of data is what database engines are designed to cope with.

Other than that when handling large quantities of data objects in C# code its best to look into using generics as this doesn't impose object instantiation in the same way that classes do and so reduces the memory footprint.

score 0 · Answer 3 · answered Feb 07 '12 at 11:57

You can use batch processing logic to fetch records in batches say 5000 records per execution and store the result in a temp dataset and once all processing is done. Dump the data from temp dataset to excel.

You can use C# BulkCopy class for this purpose.

score 0 · Answer 4 · answered Feb 07 '12 at 11:58

If it is enough to have the data available in Excel as csv you can use bulk copy

bcp "select col1, col2, col3 from database.schema.SomeTable" queryout  "c:\MyData.txt"  -c -t"," -r"\n" -S ServerName -T

This is mangnitudes faster and has little footprint.

Out of memory exception when pulling huge data from DB

4 Answers4