Solved

ADO .NET Large Data Set - DataTable Out Of Memory Exception

Posted on 2008-06-09
5
3,264 Views
Last Modified: 2013-11-26
Hi Experts!

Environment:
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?
0
Comment
Question by:Venoym
  • 2
5 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 21746031
Can you ADD GROUPING to the report...If it doesn't already have it...and flag it to GROUP ON SERVER..?

Mike
0
 

Author Comment

by:Venoym
ID: 21746610
We tried that first because we thought it was directly a Crystal Reports XI issue and it didn't help because of the number of rows returned even grouped.  I believe this is more of a .NET issue than a Crystal Reports XI issue.  I also think it's directly related to some kind of 2 GB limit in the DataTables/DataSets.
0
 

Author Comment

by:Venoym
ID: 21759668
I found a solution!

From: http://msdn.microsoft.com/en-us/library/bb147385.aspx

"      32-bit applications on 32-bit platforms can address up to 2 GB
"      32-bit applications built with the /LARGEADDRESSAWARE:YES linker flag on 32-bit Windows XP or Windows Server 2003 with the special /3gb boot option can address up to 3 GB. This constrains the kernel to only 1 GB which may cause some drivers and/or services to fail.
"      32-bit applications built with the /LARGEADDRESSAWARE:YES linker flag on 32-bit versions of Windows Vista, and on 32-bit versions of Windows Server Code Name "Longhorn" operating systems, can address memory up to the number specified by the boot configuration data (BCD) element IncreaseUserVa. IncreaseUserVa can have a value ranging from 2048, the default, to 3072 (which matches the amount of memory configured by the /3gb boot option on Windows XP). The remainder of 4 GB is allocated to the kernel and can result in failing driver and service configurations.
For more information about BCD, see Boot Configuration Data on MSDN.
"      32-bit applications on 64-bit platforms can address up to 2 GB, or up to 4 GB with the /LARGEADDRESSAWARE:YES linker flag.
"      64-bit applications use 43 bits for addressing, which provides 8 TB of virtual address for applications and 8 TB reserved for the kernel.

It is a good practice to specify large-address-aware when building 32-bit applications, by using the linker flag /LARGEADDRESSAWARE, even if the application is not intended for a 64-bit platform, because of the advantages that are gained at no cost. As explained earlier, enabling this flag for a build allows a 32-bit program to access more memory with special boot options on a 32-bit OS or on a 64-bit OS. However, developers must be careful that pointer assumptions are not made, such as assuming that the high-bit is never set in a 32-bit pointer. In general, enabling the /LARGEADDRESSAWARE flag is a good practice.


Moderator: How should I close this question?  And should points be awarded?
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22022155
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now