[ADO] leaking memory when opening/closing connection every minute
-
I am developing a VB application using ADO which polls an adabas d DB and an oracle DB every minute. It seems that this programm leaks some 15mb memory every day if I open and close the connection every minute. This behaviour doesn't occur when I open the connection just once and keep it alive. Am I doing something wrong? Is this a bug? Or is this "by design"? Michael NT 4.0 SP5 & SP6a VB6 SP5 MDAC 2.7 adabas d 11.01 oracle 8.1.5 code snippet: Public gobjOraConnection As New ADODB.Connection Public gobjOraCommand As New ADODB.Command Dim objOraRecordSet as ADODB.RecordSet ' aufbauen strConnectionString = "DSN=" & gstrOraService & ";UID=" & gstrOraUsername & ";PWD=" & gstrOraPasswort ' Verbindung herstellen gobjOraConnection.ConnectionString = strConnectionString gobjOraConnection.Open Set gobjOraCommand.ActiveConnection = gobjOraConnection ' In der Oracle DB nach Aufträgen suchen strSQL = "SELECT * FROM " & gstrOraTable & " WHERE XCHG_RESCODE IS NULL" gobjOraCommand.CommandText = strSQL ' Ergebnisobjekt zuweisen Set objOraRecordSet = gobjOraCommand.Execute("adCmdText") objOraRecordSet.Close set objOraRecordSet = Nothing Set gobjOraCommand.ActiveConnection = Nothing gobjOraConnection.Close
-
I am developing a VB application using ADO which polls an adabas d DB and an oracle DB every minute. It seems that this programm leaks some 15mb memory every day if I open and close the connection every minute. This behaviour doesn't occur when I open the connection just once and keep it alive. Am I doing something wrong? Is this a bug? Or is this "by design"? Michael NT 4.0 SP5 & SP6a VB6 SP5 MDAC 2.7 adabas d 11.01 oracle 8.1.5 code snippet: Public gobjOraConnection As New ADODB.Connection Public gobjOraCommand As New ADODB.Command Dim objOraRecordSet as ADODB.RecordSet ' aufbauen strConnectionString = "DSN=" & gstrOraService & ";UID=" & gstrOraUsername & ";PWD=" & gstrOraPasswort ' Verbindung herstellen gobjOraConnection.ConnectionString = strConnectionString gobjOraConnection.Open Set gobjOraCommand.ActiveConnection = gobjOraConnection ' In der Oracle DB nach Aufträgen suchen strSQL = "SELECT * FROM " & gstrOraTable & " WHERE XCHG_RESCODE IS NULL" gobjOraCommand.CommandText = strSQL ' Ergebnisobjekt zuweisen Set objOraRecordSet = gobjOraCommand.Execute("adCmdText") objOraRecordSet.Close set objOraRecordSet = Nothing Set gobjOraCommand.ActiveConnection = Nothing gobjOraConnection.Close
Strangely enough, I had the same problem a while ago. A colleague of mine had a similar problem a couple of years before that. Since we both work in C/C++ I don't know if this will help or not: My first attempt was like this: bar::foo() { ADO_DB db; ADO_RS rs; db.Open(); ... db.Close(); } This leaked badly. The next attempt was: bar::foo() { ADO_DB* db = new ADO_DB; ADO_RS* rs = new ADO_RS; db->Open(); ... db->Close(); delete db; } This leaked less, I don't know why, but still it leaked. My working code looks like this: class bar { ADO_DB* mdb; } bar::bar() { mdb = new ADO_DB; } bar::foo() { ADO_RS* rs = new ADO_RS; db->Open(); ... db->Close(); } Class 'bar' is instantiated once and remains active for the life of my program. In this final form, I can open and close the database as often as I like. The leak as near as I can tell occurs deep within ADO. I'm not sure about the Public declaration in VB, but it seems your code snippet is very close to my 'working' solution. Does your variable gobjOraConnection exist for the duration of the program's execution? >>>-----> MikeO
-
Strangely enough, I had the same problem a while ago. A colleague of mine had a similar problem a couple of years before that. Since we both work in C/C++ I don't know if this will help or not: My first attempt was like this: bar::foo() { ADO_DB db; ADO_RS rs; db.Open(); ... db.Close(); } This leaked badly. The next attempt was: bar::foo() { ADO_DB* db = new ADO_DB; ADO_RS* rs = new ADO_RS; db->Open(); ... db->Close(); delete db; } This leaked less, I don't know why, but still it leaked. My working code looks like this: class bar { ADO_DB* mdb; } bar::bar() { mdb = new ADO_DB; } bar::foo() { ADO_RS* rs = new ADO_RS; db->Open(); ... db->Close(); } Class 'bar' is instantiated once and remains active for the life of my program. In this final form, I can open and close the database as often as I like. The leak as near as I can tell occurs deep within ADO. I'm not sure about the Public declaration in VB, but it seems your code snippet is very close to my 'working' solution. Does your variable gobjOraConnection exist for the duration of the program's execution? >>>-----> MikeO
yes, gobjOraConnection exists for the whole life of my application. There's another things I noticed: If i close the connection, the memory needed doubles from 3.5mb to 7mb and then slowly comes down again. Strange ...
-
I am developing a VB application using ADO which polls an adabas d DB and an oracle DB every minute. It seems that this programm leaks some 15mb memory every day if I open and close the connection every minute. This behaviour doesn't occur when I open the connection just once and keep it alive. Am I doing something wrong? Is this a bug? Or is this "by design"? Michael NT 4.0 SP5 & SP6a VB6 SP5 MDAC 2.7 adabas d 11.01 oracle 8.1.5 code snippet: Public gobjOraConnection As New ADODB.Connection Public gobjOraCommand As New ADODB.Command Dim objOraRecordSet as ADODB.RecordSet ' aufbauen strConnectionString = "DSN=" & gstrOraService & ";UID=" & gstrOraUsername & ";PWD=" & gstrOraPasswort ' Verbindung herstellen gobjOraConnection.ConnectionString = strConnectionString gobjOraConnection.Open Set gobjOraCommand.ActiveConnection = gobjOraConnection ' In der Oracle DB nach Aufträgen suchen strSQL = "SELECT * FROM " & gstrOraTable & " WHERE XCHG_RESCODE IS NULL" gobjOraCommand.CommandText = strSQL ' Ergebnisobjekt zuweisen Set objOraRecordSet = gobjOraCommand.Execute("adCmdText") objOraRecordSet.Close set objOraRecordSet = Nothing Set gobjOraCommand.ActiveConnection = Nothing gobjOraConnection.Close
Instead of this: >Public gobjOraConnection As New ADODB.Connection >Public gobjOraCommand As New ADODB.Command Try this: Public gobjOraConnection As ADODB.Connection Public gobjOraCommand As ADODB.Command set gobjOraConnection = new ADODB.Connection set gobjOraCommand = new ADODB.Command Late binding is supposed to help conserve memory. The theory goes that Your program doesn’t have to check to see if you made an instance to your object every time you make a call to it. Throwing in a few set = nothings never hurts. set gobjOraCommand = nothing set gobjOraConnection = nothing Good luck!