ADO .NET Large Data Set - DataTable Out Of Memory Exception
Posted on 2008-06-09
Visual Basic 2005
Visual Studio 2005 SP1
SQL Server 2005 (on Windows Server 2003 64-bit)
Crystal Reports XI R2 SP3
Host OS - Windows XP SP2 32 bit
.NET FrameWork 1.1 (latest)
.NET FrameWork 2.0 (latest)
.NET FrameWork 3.0 (latest)
.NET FrameWork 3.5 (latest)
I am working with an error that I can't seem to find a resolution to. I have to populate a Crystal Report with data from the database. We are using a DataTable object. The query returns 24 columns from the database. The application works fine until we have about 1,400,000 rows returned. At about 1.4 million rows the DataSet/DataTable we are using causes an "System.OutOfMemoryException". I cannot reduce the number of rows or the number of columns because this report is a drill down report for statistics. In testing I was watching memory usage and found that while the host computer had 3 GB of RAM and a 3 GB pagefile, the application used about 26 MB of Active Memory and about 20 MB of VM. Once the Database pull started the amounts added 1 GB of active memory and 1 GB of VM. At the point it hits about 1 GB of each of VM and Active, the application would exception.
Is there a language barrier of 2GB for DataTables/DataSets? Is there another approach that can be used to gather the data for display?