J
JI Nieto
SUMMARY: I have created an extremely simple VB6 app that uses ADO 2.8 to
query a Access DB through OLEDB. Problem is that memory IS NOT RELEASE even
after Close & Set to Nothing both Recordset & Connection ADO objects. It is
so simple and straighforward that I can not believe anyone has not come
across with it. Can't believe it is a BUG!
Please, TRY IT! Below is the full source code. It is a tiny little app based
in Visual Basic 6 SP6 with ADO 2.80.1022.0 to SHOW how memory is NOT release
as it should.
Essentially this app just made a basic query against a local Access DB
(C:\MyDB.mdb). In my case DB size is less than 15 MBytes, compacted, and the
only table accessed holds 8 fields and 91800 records (yes, a hundred
thousand!), though the query only returns 107 records.
The problem is MEMORY RELEASE:
30 Kbytes = Compiled EXE takes 30 Kbytes on file.
3100 Kbytes = Executing the EXE takes around that and no ADO objects yet!).
5100 Kbytes = After creating (late-bound) the connection object /
recordset object.
8400 Kbytes = After executing one of the following connection strings (2
providers more or less same results):
strConnect = "driver={Microsoft Access Driver (*.mdb)};
DBQ=" & “C:\myDB.mdbâ€
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" &
“C:\myDB.mdb†& ";"
15500 Kbytes = After executing the simple query:
strSQL = "SELECT * FROM MyTable WHERE (((MyTable.OBJECT)='object') AND
((MyTable.TYPE)='type’));"
BUT THE REAL ISSUE arise when closing down & release all DB stuff:
dbRs.Close
dbConn.Close
Set dbRs = Nothing
Set dbConn = Nothing
ONLY 192 Kbytes of memory are RELEASED! (Windows Task Manager)
I tried many things to understand why and try to free some memory. Wierd
things like using 2 different forms, just in case one form was able to unload
the other (the one that creates the objects and release them) and effectively
release any memory. It doesn’t work. Same consumption.
I tried different method (early-bound, late-bound) to create the ADO
objects, same results:
(early-bound)
Dim dbConn As New ADODB.Connection
Dim dbRs As New ADODB.Recordset
(late bound)
Dim dbConn As ADODB.Connection
Dim dbRs As ADODB.Recordset
Set dbConn = New ADODB.Connection
Set dbRs = New ADODB.Recordset
I also tried different connection Modes with same results:
dbConn.Mode = adModeShareDenyNone
dbConn.Mode = adModeRead
And as far as I know these are the fastest and less memory hungry boys in
town:
dbRs.CursorType = adOpenForwardOnly
dbRs.LockType = adLockReadOnly
I have check that EVENTUALLY (minimum 15-25 min), there is some memory
released (sometimes program lower memory consumption to 5-6 Mbytes more or
less), so it seems like, EVENTUALLY, DB query cache and connection are
flushed
from program memory space. BUT NOT WHEN I TELL VB6+ADO to do it!!!!!
THIS REALLY IS HURTING ME, because I have a project in which a launch up to
100 independent instances of a bunch of different tiny programs that just
make a similar query (which is different each time, but always uses the large
table with 91800 records). A computer with 1 Gbyte of RAM more or less is
capable of launching those 100 instances (it takes too much time though). But
it doesn’t work with 512Mbytes.
Can anyone help me with these? Do I have to release something more?
Thank you very, very, very much in advance.
JI Nieto
FULL CODE:
Public Function GetParamSet() As Long
Dim dbConn As ADODB.Connection
Dim dbRs As ADODB.Recordset
Dim strSQL As String
Dim bConnectionOK As Boolean
Dim lCount As Long
Dim this_field As ADODB.Field
On Error Resume Next
strSQL = ""
lCount = 0
'This is late-bound and doesn't work either.
MsgBox "About to create ADO objects..."
Set dbConn = New ADODB.Connection
Set dbRs = New ADODB.Recordset
ObtainParamSet_Connection:
Dim strConnect As String
strDataSource = "C:\MyDB.mdb"
'new WAY, but used the same amount of memory!!!!!
strConnect = "driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
strDataSource
'Former one "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
strDataSource & ";"
dbConn.CursorLocation = adUseClient
dbConn.Mode = adModeShareDenyNone 'adModeRead do not improve
dbConn.Open strConnect, "admin", ""
strSQL = " SELECT * FROM MyTable WHERE (((MyTable.OBJECT)='object') AND
((MyTable.TYPE)='type’));"
dbRs.CursorType = adOpenForwardOnly
dbRs.LockType = adLockReadOnly
'Other method, same results dbRs.Open strSQL, dbConn
Set dbRs = dbConn.Execute(strSQL, lCount)
dbRs.Close
dbConn.Close
Set dbRs = Nothing
Set dbConn = Nothing
GetParamSet = 0
Exit Function
NOTE: I put this also in microsoft.public.access.modulesdaovba.ado before I
discover this thread. Sorry for the duplicity (see
http://www.microsoft.com/communitie....ado&mid=223a05b4-1900-4799-88d3-f9a245002154)
query a Access DB through OLEDB. Problem is that memory IS NOT RELEASE even
after Close & Set to Nothing both Recordset & Connection ADO objects. It is
so simple and straighforward that I can not believe anyone has not come
across with it. Can't believe it is a BUG!
Please, TRY IT! Below is the full source code. It is a tiny little app based
in Visual Basic 6 SP6 with ADO 2.80.1022.0 to SHOW how memory is NOT release
as it should.
Essentially this app just made a basic query against a local Access DB
(C:\MyDB.mdb). In my case DB size is less than 15 MBytes, compacted, and the
only table accessed holds 8 fields and 91800 records (yes, a hundred
thousand!), though the query only returns 107 records.
The problem is MEMORY RELEASE:
30 Kbytes = Compiled EXE takes 30 Kbytes on file.
3100 Kbytes = Executing the EXE takes around that and no ADO objects yet!).
5100 Kbytes = After creating (late-bound) the connection object /
recordset object.
8400 Kbytes = After executing one of the following connection strings (2
providers more or less same results):
strConnect = "driver={Microsoft Access Driver (*.mdb)};
DBQ=" & “C:\myDB.mdbâ€
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" &
“C:\myDB.mdb†& ";"
15500 Kbytes = After executing the simple query:
strSQL = "SELECT * FROM MyTable WHERE (((MyTable.OBJECT)='object') AND
((MyTable.TYPE)='type’));"
BUT THE REAL ISSUE arise when closing down & release all DB stuff:
dbRs.Close
dbConn.Close
Set dbRs = Nothing
Set dbConn = Nothing
ONLY 192 Kbytes of memory are RELEASED! (Windows Task Manager)
I tried many things to understand why and try to free some memory. Wierd
things like using 2 different forms, just in case one form was able to unload
the other (the one that creates the objects and release them) and effectively
release any memory. It doesn’t work. Same consumption.
I tried different method (early-bound, late-bound) to create the ADO
objects, same results:
(early-bound)
Dim dbConn As New ADODB.Connection
Dim dbRs As New ADODB.Recordset
(late bound)
Dim dbConn As ADODB.Connection
Dim dbRs As ADODB.Recordset
Set dbConn = New ADODB.Connection
Set dbRs = New ADODB.Recordset
I also tried different connection Modes with same results:
dbConn.Mode = adModeShareDenyNone
dbConn.Mode = adModeRead
And as far as I know these are the fastest and less memory hungry boys in
town:
dbRs.CursorType = adOpenForwardOnly
dbRs.LockType = adLockReadOnly
I have check that EVENTUALLY (minimum 15-25 min), there is some memory
released (sometimes program lower memory consumption to 5-6 Mbytes more or
less), so it seems like, EVENTUALLY, DB query cache and connection are
flushed
from program memory space. BUT NOT WHEN I TELL VB6+ADO to do it!!!!!
THIS REALLY IS HURTING ME, because I have a project in which a launch up to
100 independent instances of a bunch of different tiny programs that just
make a similar query (which is different each time, but always uses the large
table with 91800 records). A computer with 1 Gbyte of RAM more or less is
capable of launching those 100 instances (it takes too much time though). But
it doesn’t work with 512Mbytes.
Can anyone help me with these? Do I have to release something more?
Thank you very, very, very much in advance.
JI Nieto
FULL CODE:
Public Function GetParamSet() As Long
Dim dbConn As ADODB.Connection
Dim dbRs As ADODB.Recordset
Dim strSQL As String
Dim bConnectionOK As Boolean
Dim lCount As Long
Dim this_field As ADODB.Field
On Error Resume Next
strSQL = ""
lCount = 0
'This is late-bound and doesn't work either.
MsgBox "About to create ADO objects..."
Set dbConn = New ADODB.Connection
Set dbRs = New ADODB.Recordset
ObtainParamSet_Connection:
Dim strConnect As String
strDataSource = "C:\MyDB.mdb"
'new WAY, but used the same amount of memory!!!!!
strConnect = "driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
strDataSource
'Former one "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
strDataSource & ";"
dbConn.CursorLocation = adUseClient
dbConn.Mode = adModeShareDenyNone 'adModeRead do not improve
dbConn.Open strConnect, "admin", ""
strSQL = " SELECT * FROM MyTable WHERE (((MyTable.OBJECT)='object') AND
((MyTable.TYPE)='type’));"
dbRs.CursorType = adOpenForwardOnly
dbRs.LockType = adLockReadOnly
'Other method, same results dbRs.Open strSQL, dbConn
Set dbRs = dbConn.Execute(strSQL, lCount)
dbRs.Close
dbConn.Close
Set dbRs = Nothing
Set dbConn = Nothing
GetParamSet = 0
Exit Function
NOTE: I put this also in microsoft.public.access.modulesdaovba.ado before I
discover this thread. Sorry for the duplicity (see
http://www.microsoft.com/communitie....ado&mid=223a05b4-1900-4799-88d3-f9a245002154)