Solved

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

Posted on 2008-06-09
5
3,226 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

14 Experts available now in Live!

Get 1:1 Help Now