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

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
Webbo_1980Asked:
Who is Participating?
 
deightonConnect With a Mentor progCommented:
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
 
deightonprogCommented:
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
 
LabelsoftConnect With a Mentor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
systanCommented:
0
 
cubaman_24Commented:
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
 
systanCommented:
Accepting without comments?  I don't think that idea would work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.