DataSet object not releasing memory

[Mark Rae]

Hi,

I'm encountering a strange phenomenon whereby a DataSet object is not
releasing its memory when it's being disposed and/or set to Nothing.

It is part of a Windows service written in VB.NET which fetches data out of
a mySQL database, interrogates each row individually and takes various
actions accordingly. It can fetch upwards of 300,000 rows, each of which
vary between 1k and 2k in size, making the resulting DataSet object almost
500Mb in size. This in itself is not much of a problem, except that when the
service has finished working with the DataSet, it does not release the
memory it has been using.

I'm using it in what appears to me at least to be a fairly standard way
(code at the end of this post).

What do I have to do to free up the memory allocated to the DataSet object?
I've even tried running System.GC.Collect(), though that expectedly made no
difference.

Any assistance gratefully received.

Mark Rae


Option Explicit On
Option Strict On

Imports CoreLab.MySql
Imports System.Collections
Imports System.Data
Imports System.Xml

Public Function Import(pstrMySQLConnectString As String, pstrSQL As String)
As Boolean

Dim objMySQL As New CMySQLCoreLab(pstrMySQLConnectString)
Dim objMySQLDS As DataSet

objMySQLDS = objMySQL.GetDataSet(pstrSQL)

For Each objRow As DataRow in objMySQLDS.Tables(0).Rows
'
' do the processing
'
Next

objMySQLDS.Dispose
objMySQLDS = Nothing
objMySQL.Dispose
objMySQL = Nothing

End Function

 

[William \(Bill\) Vaughn]

Remember the garbage collector in VB.NET is not the same as used in VB6.
VB.NET's GC only runs (and frees memory) when free memory is exhausted.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Robbe Morris [C# MVP]]

You are running a query in a windows service that returns 500 MB worth
of data in a single query???

Are you really sure that is a good idea? I would suspect
your memory problems would go bye, bye if you ran multiple
queries and grabbed a group of records at a time either by
date range or some other characteristic. Your app will no
doubt process data much faster and not drag the overall
performance of the server down.



--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

 

[Mark Rae]

You are running a query in a windows service that returns 500 MB worth
of data in a single query???

Are you really sure that is a good idea? I would suspect
your memory problems would go bye, bye if you ran multiple
queries and grabbed a group of records at a time either by
date range or some other characteristic. Your app will no
doubt process data much faster and not drag the overall
performance of the server down.



--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

 

[Mark Rae]

I would suspect your memory problems would go bye, bye if you ran multiple
queries and grabbed a group of records at a time either by
date range or some other characteristic. Your app will no
doubt process data much faster and not drag the overall
performance of the server down.

Makes no difference if I grab 300,000 records once, or 1 record 300,000
times - the memory isn't released either way...

 

[Robbe Morris [C# MVP]]

You may want to post some of your ADO.NET code
and your SQL Server code (if it is a proc).

I expect that it would not take your memory down to
where it was just prior to the first query. But, it
certainly shouldn't take up 500MB and attribute
it to your specific .NET process. If SQL Server's
memory process goes up and stays up, I could understand
that.

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

 

[Mark Rae]

You may want to post some of your ADO.NET code
and your SQL Server code (if it is a proc).

I did in my original post - have added it again to the bottom of this one.

I expect that it would not take your memory down to
where it was just prior to the first query. But, it
certainly shouldn't take up 500MB and attribute
it to your specific .NET process. If SQL Server's
memory process goes up and stays up, I could understand
that.

Option Explicit On
Option Strict On

Imports CoreLab.MySql
Imports System.Collections
Imports System.Data
Imports System.Xml

Public Function Import(pstrMySQLConnectString As String, pstrSQL As String)
As Boolean

Dim objMySQL As New CMySQLCoreLab(pstrMySQLConnectString)
Dim objMySQLDS As DataSet

objMySQLDS = objMySQL.GetDataSet(pstrSQL)

For Each objRow As DataRow in objMySQLDS.Tables(0).Rows
'
' do the processing
'
Next

objMySQLDS.Dispose
objMySQL.Dispose

End Function