Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 4-Jan-12 2:41am
m_abh350
Edited 4-Jan-12 12:00pm
v5
Comments
Wonde Tadesse at 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 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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?[^]
  Permalink  
Comments
m_abh at 4-Jan-12 8:24am
   
Datatable has about 90 columns and 1000,000 rows
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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[^]
  Permalink  
Comments
Marcus Kramer at 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.
Mika Wendelius at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  
Comments
m_abh at 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.

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

  Print Answers RSS
0 OriginalGriff 400
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 DamithSL 95
4 Garth J Lancaster 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 4 Jan 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100