Exporting Large Amounts of Data

Question

Here is my issue..

We have 2 types of reports in our site, data shown in grid and data instantly downloaded as a report.

These reports can contain a few years of data (1+ million rows), we have been allowing our customers to download the data against a date range, but we have started limited how long they can view data to prevent performance issues in our site. However, the data is still getting pretty large even on a small date range, now that they are expanding, and if they download too much, our memory spikes over a few gigs and run out of memory.

Question I have is, I rather not limit their data so I'm trying to figure out a good solution to allow them to download as much as they want.

I can limit what they see by only returning data per page so there is no performance issues, however downloading is always the problem.

I've looked into async but haven't successfully been able to get that to work, as it spikes the memory when I'm loading the data.

Ideas? Thoughts? Suggestions?

Code Example:


// Get Data

SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da;
DataSet ds = new DataSet();

con.ConnectionString = "MyConnectionString";
con.Open();

cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "MyStoredProc";
da = new SqlDataAdapter(cmd);
da.Fill(ds);

con.Close();

StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
DataGrid dg = new DataGrid();
dg.DataSource = ds.Tables[0];
dg.DataBind();
dg.RenderControl(htw);

Response.ClearContent();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=Report.xls");
Response.Write(sw.ToString());
Response.End();

When I run this with my data.. which is approximately 800k rows, my memory spikes and I get an out of memory error, and make things worse.. it always hogs up at the RenderControl until its done

Why not just throw hardware at the problem and add a few more RAM sticks? They're pretty cheap these days. — ashes999
That is not an option, can't just increase ram because of memory issues, especially since the reports usually spike up to 4GB — jaekie

coder net coder net · Accepted Answer · 2011-10-07T14:15:30

i'm assuming the data comes from a backend database. If so, you should not have the user wait for this operation to complete. It is a bad UI design, esp when the memory can go upto 4GB.

I agree with the other suggestions that you should look into improving your code and design probably to help reduce the footprint. But regardless, you should have something like a scheduled job architecture for this.

You let the user to hit download on a search/file and it gets added to a queue in a database table. There is a db/.net process that comes along and processes these jobs and generate a file in the proper format on the server. It may be possible to reuse the file across many users if the data is the same and you use proper naming conventions. The user should then be able to go to a download queue page and see all his downloads that he has scheduled. Once complete, he will be able to download the file.

if you have a requirement that will not let you do this, please put a comment explaining it.

Exporting Large Amounts of Data

4 Answers