Solved

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

Posted on 2011-03-24
6
580 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

810 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