best way to use .net Datatable with a huge data

Question

1.44/5 (2 votes)

See more:

, +

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 1:41am

m_abh

Updated 18-Jan-18 19:20pm

Wonde Tadesse

v5

Add a Solution

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.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Carlos A Merighe · Answer 1 · 2016-03-23T10:30:00

Solution 4

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

Posted 23-Mar-16 10:30am

Carlos A Merighe

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

RaviRanjanKr · Answer 2 · 2012-01-04T11:26:00

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.

Posted 4-Jan-12 11:26am

RaviRanjanKr

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.

Wendelius · Answer 3 · 2012-01-04T10:48:00

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[^]

Posted 4-Jan-12 10:48am

Wendelius

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

thatraja · Answer 4 · 2012-01-04T01:58:00

How many rows in your DataTable?

Dave Kreskowiak wrote[^]:
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.

From MSDN[^]:
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?[^]

best way to use .net Datatable with a huge data

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us