J
JI Nieto
Memory Not Immidiately Released using ADO 2.8
I have a tiny little app based in Visual Basic 6 SP6 with ADO 2.80.1022.0.
It comprises a dual dialog (more below) with a handful of objects. I have
created to understand WHY ADO do not release memory immediately after closing
and set to nothing both the ADODB.Connection and the ADODB.Recordset objects.
Essentially I‘ve just made a basic query against an Access DB that is local
(C:\). DB size is less than 15 MBytes and the only table accessed holds 91800
records (yes, a hundred thousand!), though it returns 107 records.
The problem is that the compiled EXE takes 30 Kbytes of memory initially.
Then jumps to around 3500 Kbytes after creating the connection object /
recordset object. When executing the following connection strings (more or
less same results) it jumps again to 8 Mbytes:
strConnect = "driver={Microsoft Access Driver (*.mdb)}; DBQ=" & “C:\myDB.mdbâ€
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
“C:\myDB.mdb†& ";"
Then it reach 15 Mbytes when executing the simple query:
strSQL = "SELECT * FROM MyTable WHERE (((MyTable.OBJECT)='object') AND
((MyTable.TYPE)='type’));"
And the problem arise when closing down & release all DB stuff:
dbRs.Close
dbConn.Close
Set dbRs = Nothing
Set dbConn = Nothing
Because the Windows Task Manager says that ONLY 192Kbytes are RELEASED!!!!!!
I tried to use 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 creating method (early-bound, late-bound), 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 to different Modes in connection 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-20 min), there is some memory
released (sometimes program lower memory consumption to 4 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 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
I have a tiny little app based in Visual Basic 6 SP6 with ADO 2.80.1022.0.
It comprises a dual dialog (more below) with a handful of objects. I have
created to understand WHY ADO do not release memory immediately after closing
and set to nothing both the ADODB.Connection and the ADODB.Recordset objects.
Essentially I‘ve just made a basic query against an Access DB that is local
(C:\). DB size is less than 15 MBytes and the only table accessed holds 91800
records (yes, a hundred thousand!), though it returns 107 records.
The problem is that the compiled EXE takes 30 Kbytes of memory initially.
Then jumps to around 3500 Kbytes after creating the connection object /
recordset object. When executing the following connection strings (more or
less same results) it jumps again to 8 Mbytes:
strConnect = "driver={Microsoft Access Driver (*.mdb)}; DBQ=" & “C:\myDB.mdbâ€
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
“C:\myDB.mdb†& ";"
Then it reach 15 Mbytes when executing the simple query:
strSQL = "SELECT * FROM MyTable WHERE (((MyTable.OBJECT)='object') AND
((MyTable.TYPE)='type’));"
And the problem arise when closing down & release all DB stuff:
dbRs.Close
dbConn.Close
Set dbRs = Nothing
Set dbConn = Nothing
Because the Windows Task Manager says that ONLY 192Kbytes are RELEASED!!!!!!
I tried to use 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 creating method (early-bound, late-bound), 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 to different Modes in connection 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-20 min), there is some memory
released (sometimes program lower memory consumption to 4 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 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