System.OutOfMemoryException importing Page with a single large DataGrid into Excel (Quick Fix)

Question

We use Excel for a number of ad-hoc pivots / reports.

To get the data into Excel we have a general page with a simple DataGrid that we bind from a DataSet / DataTable. We "Import External Data" using this URL in Excel.

Unfortunately we have a query that returns around 100 columns and 40k rows. The Application server only has 2GB of RAM and the used memory jumps up by 1 gig and then causes a System.OutOfMemoryException.

I intend to rewrite the page that produces the DataGrid to manually create an HTML table by looping through a DataReader rather than loading it all into a DataTable. I also intend to put more memory into the server.

My question is, how can I get this one spreadsheet to update right now? Is there any quick fix I can do to the DataGrid to temporarily let it work? I have already turned ViewState off for the DataGrid.

Answer 1

I don't think it's the DataGrid that is causing your out of memory exection, but rather your DataSet. We had this same problem where we had tens of thousands of records being populated into a treeview from a DataSet, resulting in slow load times. The reason is because the DataSet will load ALL of the data that is being queried, not just what is seen. There are two workarounds for this: create a just-in-time loader (using DataReader) that will retrieve your data as it is needed (of course, you then have the overhead of managing what data is or isn't local) or refine your DataSet query to reduce the number of records returned. I'm guessing the latter will be more appropriate for your current situation.