VB > ADO > Access Memory Leak/Creep

[Gman]

Hi NG,

I have a VB app that connects thru ADO to an Access DB. While performing
pretty heavy testing I have noticed that my memory usage increases -
after, say, 1000 queries my application's memory consumption may go from
20MB to 40MB.

I use one connection to connection to the database, persisting the
connection between queries. If I close the connection after every query
then this cures the creep - but I don't want to take this hit in
performance.

There is an MS KB article that mentions a similar issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q247140
but says it is cured in Jet SP6 - I have SP8 (4.0.8618.0)

Is there something else I can do to tidy up after querying w/o closing
the connection - I'm already closing the record set, setting it to nothing?

Thanks for any suggestions.
Gman

FYI
I connect as follows:

Using objects with module scope
Private m_rs New ADODB.Recordset
Private m_cn As New ADODB.Connection

I use one function to make the connection then a second function that
retrieves a query's results and places them into a variant (function
tidied up for brevity - error trapping removed for instance):
Function RunQuery(mySQL as string) as variant
With m_rs
.ActiveConnection = m_cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open mySQL

'Dump the record set into an array
If .RecordCount = 0 Then
ADO_RunQuery = False
Else
ADO_RunQuery = .GetRows
End If

.Close

End With
'tidy up
Set m_rs = Nothing

End Function

 

[Gman]

Addendum:

I notice that both:
m_rs.ActiveConnection = m_cn
and
set m_rs.ActiveConnection = m_cn
are tolerated. I think this is the only instance I've ever seen in VB6
where the use of "set" is optional - at least appears to be.

 

[Norman Yuan]

In ADO, RecordSet.ActiveConnection is VARIANT type, not a ADODB.Connection
type. So you can assign it to a Connection object (without using "Set"), or
a connection string.

When you assign a connectionString rather tahn a ADODB.Connection to
recordSet.ActiveConnection, and open the recordSet, a new connection is
created and opened automatically. This connection is supposed to be closed
when the RecordSet is closed.

Could this the source of your problem? You may want to examine your code to
make sure the variable "m_cn" is an instantiated ADODB.Connection object,
not just a ConnectionString (meaning the connection is explicitly created,
not implicitly created by ADO based on a connection string) before assigning
it to the RecordSet. Then test it to see what happens

 

[Gman]

Hi Norman,

Re. ActiveConnection being a variant: Of course!! I should have realised
that - thanks for pointing that out to me.

Thanks for your suggested solution. Unfortunately, m_cn is declared:
Private m_cn As New ADODB.Connection
at the top of the module and there are no other variables with the same
name. :-( Furthermore, I explicitly check it's open before opening the
record set. I've also checked the number of connections to the DB and
that's remaining at 1 also.

From further googling I'm starting to get the impression that this
might be by design:
From http://makeashorterlink.com/?Z59231C7C
<<Jet is doing some caching on the connection and rather than slow
performance by garbage collecting while the connection is open, Jet
waits until the connection is closed and then cleans up.>>

As far as I can tell, the problem is definitely related to caching. My
application shows a week of data at a glance (about 300 records per
week). The user can navigate back and forwards between weeks. If I go
forward through four weeks and then back four repeatedly, the memory
increases then plateaus -- it's obviously cached these weeks. If, say, I
then choose to hop to weeks 5 through 8 too, it will bloat again and
then plateau. Therefore, it's not really a leak per se - the memory will
be released when the app is closed.

To fix it, I've done this: whenever user navigates to a new week, I
close the connection and reopen. The user can then perform whatever they
need to on that week (depending on what they're doing it could be
another 10 or 100 small queries) on the same connection -- but it's
annoying nonetheless.

Thanks again for your advice -- anything further gratefully received also.

Thanks