Click here to Skip to main content
15,850,750 members
Please Sign up or sign in to vote.
1.44/5 (2 votes)
Hi All,
I need your help please in very urgent issue, I need to fill a .net datatable with a huge data( about 400 MB), when load data i face a memory problem , Out of memory exception , i found it take more than 1GB from memory.


I search about this problem and find somebody talk that when use .Net datatable it makes 3 copies of this .net datatable.


My Question is:
what is the best way to use .net Datatable with a huge data?
to avoid memory problems??
Thanks in advance

Posted
Updated 18-Jan-18 20:20pm
v5
Comments
Wonde Tadesse 4-Jan-12 17:47pm    
Why you want to fill such huge data at a time is a puzzle and bad programming practice. Use pagination technique as much as possible.
m_abh 11-Jan-12 16:59pm    
I am so sorry for not reply you , and your replies are very thankful.
but the problem i face it is i have sql return datatable has 1000 column and about 1000,000 rows, the columns types are double and int.
this data returned size is about 200-400 MB , but when added to .net DataTable the size is grow to 800MB - 1GB.
so why this happen in .net dataTable?? and is there any way to avoid this??
my problem is in Memory performance issue.

Thanks.

How many rows in your DataTable?
If you have to hit that limit, you've got bigger issues with your application...

It's System.Int32.MaxValue, or 2,147,483,647. But since you wont be able to reach that limit without generating an OutOfMemory Exception, it's limited by available memory.

To add rows to a DataTable, you must first use the NewRow method to return a new DataRow object. The NewRow method returns a row with the schema of the DataTable, as it is defined by the table's DataColumnCollection. The maximum number of rows that a DataTable can store is 16,777,216. For more information, see Adding Data to a DataTable.

Best way is split the DataTable
How to Split one datatable to many datatables?[^]
 
Share this answer
 
Comments
m_abh 4-Jan-12 8:24am    
Datatable has about 90 columns and 1000,000 rows
Best Practices for Using ADO.NET[^]
This article provides you with the best solutions for implementing and achieving optimal performance, scalability, and functionality in your Microsoft ADO.NET applications; it also covers best practices when using objects available in ADO.NET and offers suggestions that can help you optimize the design of your ADO.NET application.
 
Share this answer
 
Comments
m_abh 12-Jan-12 2:29am    
I am so sorry for not reply you, i was very busy, and your reply are very thankful.
but the problem i face it is i have sql return datatable has 1000 column and about 1000,000 rows, the columns types are double and int.
this data returned size is about 200-400 MB , but when added to .net DataTable the size is grow to 800MB - 1GB.
so why this happen in .net dataTable?? and is there any way to a void this??
my problem is in Memory performance issue.

Thanks.
Since the amount of data is quite big, in my opinion you should store the data for example in a database and fetch only a necessary portion of it whenever needed. For example you can use:
- SQL Server Compact Edition[^]
- Microsoft SQL Server Express Edition[^]
 
Share this answer
 
Comments
fjdiewornncalwe 4-Jan-12 17:03pm    
Agreed. When an issue like this comes up, it is usually the architecture and the model that have the issue. There really is no excuse for needing a million rows loaded at a given time.
Wendelius 4-Jan-12 17:22pm    
Agree, and the datatable isn't designed to be a persistent data storage even though it can be used as such
I had a problem with a DataTable throwing an out of memory exception randomly. As it turned out, the project's Build Platform target was set to "Prefer 32-bit". Once I unselected that option, the random out of memory exception went away.

Carlos A Merighe
 
Share this answer
 
Comments
CHill60 23-Mar-16 16:37pm    
Until the data grows so big it comes back. I advise not posting answers to very old posts in this way

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900