Solved

How do i free up memory used by a datatables stored in memory

Posted on 2011-03-24
6
573 Views
Last Modified: 2012-08-14
I have a datatable which i'm populating in memory with 5million rows. The idea is that i do this in batches so for every million i clear the variable and start again by running the the following code.
    dt.Dispose()
            dt = Nothing
            dt = New DataTable
            dt = GetDataTableSchema()
            tempRow = Nothing
            tempRow = dt.NewRow
            GC.Collect()

Open in new window


However for some reason this doesnt free the memory straight away, and my application is running out of memory.

Can anyone tell me what im doing wrong here?

Thanks
Webbo
0
Comment
Question by:Webbo_1980
6 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 35206183
I've done this 'batching' myself, and for large batches you risk blowing the memory I found.  

As far as I can see, your code makes the data table eligible for garbage collection, but a datatable implements the Idisposable interface, and is added to the finalization queue prior to its final reclamation of memory.  

I would recommend reducing the size of the batch you are processing at once.  Also then you will be able to dispense with the GC.collect

0
 
LVL 18

Accepted Solution

by:
deighton earned 250 total points
ID: 35206211
a better structure would be to call a function or sub for each batch

Sub ProcessBatch()

  Using dt as new DataTable

    .........your processing here


  End Using

end sub

version 2005+
Using takes care of dispose for you, however it will not cause garbage collection to speed up, but is considered a better form.
0
 
LVL 3

Assisted Solution

by:Labelsoft
Labelsoft earned 250 total points
ID: 35206441
Is this a piece of code you call for every run? So like:

Public Property Rows As List(Of DataRow)

Public Sub GetNext5MillionRows()

Dim dt as DataTable = Nothing
dt = GetDataTableSchema()
Dim tempRow as DataRow
tempRow = newRow

Try

'Do something with the data, like:
Rows.Add(tempRow)

Finally

tempRow.Dispose()
tempRow = Nothing
dt.Dispose()
dt = Nothing

GC.Collect
End Try

End Sub

'Call the sub
GetNext5MillionRows()

In other words... There might be a few potential problems with your sample. If you don't clear tempRow appropiatly it's property Table counts as a reference to the DataTable instance.

Plus the order in which things occur right now are not optimal I think.

Run #1:
You retrieve 5 million rows.

Run #2:
- You still have the 5 million rows (GC.Collect has not been called yet for the 1st instance of DataTable)
- You retrieve the next 5 million rows (You've got 10 million rows right now in memory)

So like I said, the order is not optimal.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 14

Expert Comment

by:systan
ID: 35206813
0
 
LVL 8

Expert Comment

by:cubaman_24
ID: 35214170
Hello:
Why you would want to load 5.000.000 rows in memory? It won't be better to program your ETL in transact sql and execute directly in DDBB, avoiding memory and network overhead?
Best regards.
0
 
LVL 14

Expert Comment

by:systan
ID: 35226129
Accepting without comments?  I don't think that idea would work.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

10 Experts available now in Live!

Get 1:1 Help Now