Solved

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

Posted on 2011-03-24
6
578 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
DevOps Toolchain Recommendations

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

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 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

20 Experts available now in Live!

Get 1:1 Help Now