Last post Jun 02, 2010 05:03 AM by priyankar.dutta
Jun 01, 2010 08:48 AM|priyankar.dutta|LINK
I have a Oracle database and am using ODP.NET for connecting to the database (instead of OracleClient). I have a GridView in my page. The query to bind the gridview is dynamically formed and sometimes it returns over 200,000 rows (maybe more). I have paging
implemented (by 100 rows). I tend to get the OutOfMemoryException whenever the query is returning over 200,000 rows.
Just a sample of what is being done :
DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(query,connectionProperties);
The dataset ds is used as DataSource of the grid. I am not using any sort of arrays and only two session variables. Any way to resolve this by not changing any server configuration?
Thanks in advance
Jun 01, 2010 08:57 AM|NihirPorecha|LINK
Use database side paging. It means, fetch only 100rows at a time and display them into the grid.
Jun 01, 2010 09:06 AM|frez|LINK
You must change your query or stored proedure to only return a page of data at a time. With SQL Server you would do this by utilising the ROW_NUMBER function, I am not familiar with Oracle and whether it has an equivalent.
Have a look at Scott Mitchell's article here http://www.4guysfromrolla.com/articles/031506-1.aspx at using the ObjectDataSource and paging.
The solution you have implemented is simply not scalable.
Jun 01, 2010 09:48 AM|priyankar.dutta|LINK
Thanks you for replying. Now I am retrieving only the rows for a particular page from the database. This solves that problem. But I also have a Export To CSV in my page and for that I have to use the entire dataset (can't do it on page by page basis). Any
workarounds for that?
I am getting the OutOfMemoryException on trying to Export to CSV now.
Appreciate the help.
Thank you for replying. Now I am retrieving only the rows for a particular page from the database. This solves that problem. But I also have a Export To CSV in my page and for that I have to use the entire dataset (can't do it on page by page basis). Any
workarounds for that?
Jun 01, 2010 11:40 AM|frez|LINK
There is no reason why you couldn't build the CSV file yourself using a suitable recordset.
Try a forward only, read only cursor and if that breaks with the whole amount of data try reading the data in a 'page' at a time similar to your grid, except this time your page could be say 1000 rows.
Jun 02, 2010 05:03 AM|priyankar.dutta|LINK
The idea was good and even though it took me a load of time to do it for the CSV (as my original query had to be fetched from db) eventually it worked. Guess handling large large datasets is something that requires 64 bit. Anyways thanks for the help...
One thing though fetching 30000 records at a time (which I am doing now) makes the CSV export very very slow. Will have to live with that now !!! Will post back here for the benefit of others if I get a workaround...