Populating DataTable - OutOfMemory Exception

[Mantorok]

Hi all

I'm populating a DataTable with around 1,500,000 rows, the table contains 4
columns, 3 string columns and a decimal column.

However, I keep getting OutOfMemory exceptions when my app starts to reach
around 700MB memory (this is a console app by the way).

So, question is, why does the DT eat so much memory and how can I avoid
these OutOfMemory exceptions? I "need" all of these rows available because
they will be referenced throughout the lifecycle of my app.

Thanks
Kev

 

[Jesse Houwing]

Hello Mantorok,

Hi all

I'm populating a DataTable with around 1,500,000 rows, the table
contains 4 columns, 3 string columns and a decimal column.

However, I keep getting OutOfMemory exceptions when my app starts to
reach around 700MB memory (this is a console app by the way).

So, question is, why does the DT eat so much memory and how can I
avoid these OutOfMemory exceptions? I "need" all of these rows
available because they will be referenced throughout the lifecycle of
my app.

Thanks
Kev

DataTable contains much more than just teh values. And it is made up from
a lot of different classes that also remain in memory (DataColumn, DataRows,
etc). Why not use a simpler construct.

public class MyImportantThing
{
private string _string1;
private string _string2;
private string _string3;
private decimal _decimal1;

public MuImportantThing(string s1, string s2, string s3, decimal d1)
{
_string1 = s1;
...
...
_decimal1 = d1;
}

// Properties here
}

And then instantiate a List<MyImportantThing> this should be much faster.
If you need find/select capabilities you can add these easily if you create
your own list type:

public class MyImportantThingCollection: List<MyImportantThing>
{
public MyImportantThing FindByString1(string s1)
{
foreach (MyImportantThing mip in this)
{
if (string.Equals(mip.String1, s1)
{
return mip;
}
}
}
}

If you need fast access through one of the properties of you class, you could
always decide to use a Disctionary<>

public class MyImportantThingDictionary: Dictionary<MyImportantThing>
{
}

This will have a much lower memory footprint.

I would also consider a completely different scenario... Don't load a list
of 1500000 items at all and get the item you need from a database when you
need them. That insures you only load the rows you absolutely need. You could
even load the item when it's actually being used, instead of pre-loading
them all. But I guess you have your reasons.

Jesse

 

[Mantorok]

Hi Jesse

Thanks for your response, it has prompted me to re-think why I need all
these records, and because of that I've just realised I don't need all of
the records because I could be filtering them at the DB anyway.

Anyhow, the solution regarding custom classes wouldn't apply to my
situation, the DataTables are being returned from 7 web services that we
have in 7 of our remote data sources (the WS takes an SQL statement and
returns the results).

But thanks anyway, I'm going to refactor my code now and should have a much
more efficient solution.

Thanks
Kev

 

[Jesse Houwing]

Hello Mantorok,

Hi Jesse

Thanks for your response, it has prompted me to re-think why I need
all these records, and because of that I've just realised I don't need
all of the records because I could be filtering them at the DB anyway.

Anyhow, the solution regarding custom classes wouldn't apply to my
situation, the DataTables are being returned from 7 web services that
we have in 7 of our remote data sources (the WS takes an SQL statement
and returns the results).

But thanks anyway, I'm going to refactor my code now and should have a
much more efficient solution.

Thanks
Kev

You're welcome

You could still put the contents of teh datatables in a custom class. You
could then throw away the datatables.

Jesse

 

[Mantorok]

You're welcome

You could still put the contents of teh datatables in a custom class.
You could then throw away the datatables.

True, however I have a function that queries all 7 sources for me and stuffs
all the results into 1 fat table, and this is where it runs out of memory.
If I created custom classes I would have to pretty much re-write this function
for this 1 purpose, which isn't favourable.

It was sloppy of me not to restrict the data-set coming back, I should know
better but I never thought about it for this particular task.

Kev

 

[Jesse Houwing]

Hello Mantorok,

True, however I have a function that queries all 7 sources for me and
stuffs
all the results into 1 fat table, and this is where it runs out of
memory.
If I created custom classes I would have to pretty much re-write this
function
for this 1 purpose, which isn't favourable.
It was sloppy of me not to restrict the data-set coming back, I should
know better but I never thought about it for this particular task.

Kev

We all make mistakes . Good luck!

Jesse

 

[Ignacio Machin \( .NET/ C# MVP \)]

HI,

In any case 1.5M of rows is TOO much for anything useful.

You better keep them in a DB.

Any operation will take a LONG time in a table with 1.5M rows.

 

[Ignacio Machin \( .NET/ C# MVP \)]

HI,

Hi Jesse

Thanks for your response, it has prompted me to re-think why I need all
these records, and because of that I've just realised I don't need all of
the records because I could be filtering them at the DB anyway.

Anyhow, the solution regarding custom classes wouldn't apply to my
situation, the DataTables are being returned from 7 web services that we
have in 7 of our remote data sources (the WS takes an SQL statement and
returns the results).

But thanks anyway, I'm going to refactor my code now and should have a
much more efficient solution.

How ofter you run this?

I would suggest you to insert all those results in a local SQL DB and then
make a query to the local data.

 

[Mantorok]

HI,

In any case 1.5M of rows is TOO much for anything useful.

You better keep them in a DB.

Any operation will take a LONG time in a table with 1.5M rows.

Yep - I think my brain was on vacation the day I wrote this app

I was going about it the completely wrong way, all this time I could've reduced
the result set and I didn't even think to, anyway it's all ok now.

Thanks
Kev