Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-24
6
Medium Priority
?
587 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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 1000 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
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

670 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