Memory "leaking" from ADO!!! Help!

  • Thread starter Sander Verhagen
  • Start date
S

Sander Verhagen

Hi,


Using ADO I'm experiencing what I would call a memory leak. It might
formally not be one, because I have no prove that my application is not
nicely releasing the data on exit, but every time the code below is called,
the memory use of my application (as Windows Task Manager shows it) is
increased. As far as I can see, the amount of memory use increase depends on
the size of the retrieved record set.

I've included my Open function implementation from which the ADO connection
is opened (just once). Also I've included one of my LoadRecords functions
where it seems to go wrong. Stepping into my code seems to show that the
memory is increased when I call Execute, thus when a record set is opened,
which isn't strange, but it should be released once I leave the function,
shouldn't it? Do I close up my record sets incorrectly after use, or what's
wrong here?

// typical connection string:
// Provider=sqloledb;Data Source=...;Initial Catalog=...;User
Id=sa;Password=...
// yes, it's a MS SQL Server
SCCS Open(LPCSTR connectionstring)
{
::CoInitialize(NULL);
try
{
// Create an "empty" connection
m_hresult = m_pConnection.CreateInstance(__uuidof(Connection));
if( SUCCEEDED(m_hresult) )
{
// Open a designated connection
m_hresult = m_pConnection->Open(connectionstring, "", "",
adConnectUnspecified);
if( SUCCEEDED(m_hresult) )
{
m_pConnection->CursorLocation = adUseClient;
return SUCCESS;
}
}
}
catch(_com_error &e)
{
m_hresult = -1;
DoError(e);
}
return FAIL;
}

SCCS LoadRecords( ... )
{
if( IsOpen() )
{
// Format a SQL string to query the records with
CString sql;
/* here goes the code to set up the SQL string */

ThreadLock();
try
{
// Open a recordset with the made up SQL string
COleVariant RecordsAffected;
_RecordsetPtr set;
set = m_pConnection->Execute( sql.AllocSysString(),
RecordsAffected, adCmdText );
// Check if anything in recordset
if( !(set->EndOfFile && set->BOF) )
{
// Get to first record
set->MoveFirst();
while( !set->EndOfFile )
{
/* put the retrieved records into memory objects, here */
/* I'm pretty darn quite sure that this isn't where the
problem is */
}
}
set->Close();
set = 0;
ThreadUnlock();
return SUCCESS;
}
catch(_com_error &e)
{
DoError(e);
}
ThreadUnlock();
}
return FAIL;
}

#define SCCS BOOL
#define SUCCESS FALSE
#define FAIL TRUE

This code isn't ready to compile for you, guys, but it is semantically
exactly what I am doing.

Where is my memory going??? *cry* My application is one that has to run for
days... well, actually "forever", but I'd settle for "for days" now. What
happens now is that it'll be using soooo much memory after a day that
restarting the application is just plain wise. (That really solves the
problem, so it looks that at that time all this memory is actually
released.)

I would be sooo grateful for you helping me out on this, as this has been
driving me up the walls for days now!!!

All the best,


Sander Verhagen
[ (e-mail address removed) ]
 
J

Jeff Weyrum

Sander Verhagen said:
Hi,


Using ADO I'm experiencing what I would call a memory leak. It might
formally not be one, because I have no prove that my application is not
nicely releasing the data on exit, but every time the code below is called,
the memory use of my application (as Windows Task Manager shows it) is
increased. As far as I can see, the amount of memory use increase depends on
the size of the retrieved record set.

I've included my Open function implementation from which the ADO connection
is opened (just once). Also I've included one of my LoadRecords functions
where it seems to go wrong. Stepping into my code seems to show that the
memory is increased when I call Execute, thus when a record set is opened,
which isn't strange, but it should be released once I leave the function,
shouldn't it? Do I close up my record sets incorrectly after use, or what's
wrong here?

// typical connection string:
// Provider=sqloledb;Data Source=...;Initial Catalog=...;User
Id=sa;Password=...
// yes, it's a MS SQL Server
SCCS Open(LPCSTR connectionstring)
{
::CoInitialize(NULL);
try
{
// Create an "empty" connection
m_hresult = m_pConnection.CreateInstance(__uuidof(Connection));
if( SUCCEEDED(m_hresult) )
{
// Open a designated connection
m_hresult = m_pConnection->Open(connectionstring, "", "",
adConnectUnspecified);
if( SUCCEEDED(m_hresult) )
{
m_pConnection->CursorLocation = adUseClient;
return SUCCESS;
}
}
}
catch(_com_error &e)
{
m_hresult = -1;
DoError(e);
}
return FAIL;
}

SCCS LoadRecords( ... )
{
if( IsOpen() )
{
// Format a SQL string to query the records with
CString sql;
/* here goes the code to set up the SQL string */

ThreadLock();
try
{
// Open a recordset with the made up SQL string
COleVariant RecordsAffected;
_RecordsetPtr set;
set = m_pConnection->Execute( sql.AllocSysString(),
RecordsAffected, adCmdText );
// Check if anything in recordset
if( !(set->EndOfFile && set->BOF) )
{
// Get to first record
set->MoveFirst();
while( !set->EndOfFile )
{
/* put the retrieved records into memory objects, here */
/* I'm pretty darn quite sure that this isn't where the
problem is */
}
}
set->Close();
set = 0;
ThreadUnlock();
return SUCCESS;
}
catch(_com_error &e)
{
DoError(e);
}
ThreadUnlock();
}
return FAIL;
}

#define SCCS BOOL
#define SUCCESS FALSE
#define FAIL TRUE

This code isn't ready to compile for you, guys, but it is semantically
exactly what I am doing.

Where is my memory going??? *cry* My application is one that has to run for
days... well, actually "forever", but I'd settle for "for days" now. What
happens now is that it'll be using soooo much memory after a day that
restarting the application is just plain wise. (That really solves the
problem, so it looks that at that time all this memory is actually
released.)

I would be sooo grateful for you helping me out on this, as this has been
driving me up the walls for days now!!!

All the best,


Sander Verhagen
[ (e-mail address removed) ]

Without doing all the troubleshooting. the most common problem is not
calling the destructor of the instance.
you do close the recordset but setting "set" to 0. only seems to me to
change your recordptr to 0 not call the destructor.
 
M

Marshall Smith

I'm having a little trouble making out your code, but it doesn't look like
you ever release the memory used by the recordset object. If you start the
procedure with:

Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim cmd as ADODB.Command
Dim prm as ADODB.Parameter

then make sure you end it with:

Set cnn = Nothing
Set rs = Nothing
Set cmd = Nothing
Set prm = Nothing

And, make sure that it will hit those Set statements even if it runs into an
error. That will release all of the memory being used by those objects.

--
HTH,

Marshall Smith
Project Developers, Inc.


Sander Verhagen said:
Hi,


Using ADO I'm experiencing what I would call a memory leak. It might
formally not be one, because I have no prove that my application is not
nicely releasing the data on exit, but every time the code below is called,
the memory use of my application (as Windows Task Manager shows it) is
increased. As far as I can see, the amount of memory use increase depends on
the size of the retrieved record set.

I've included my Open function implementation from which the ADO connection
is opened (just once). Also I've included one of my LoadRecords functions
where it seems to go wrong. Stepping into my code seems to show that the
memory is increased when I call Execute, thus when a record set is opened,
which isn't strange, but it should be released once I leave the function,
shouldn't it? Do I close up my record sets incorrectly after use, or what's
wrong here?

// typical connection string:
// Provider=sqloledb;Data Source=...;Initial Catalog=...;User
Id=sa;Password=...
// yes, it's a MS SQL Server
SCCS Open(LPCSTR connectionstring)
{
::CoInitialize(NULL);
try
{
// Create an "empty" connection
m_hresult = m_pConnection.CreateInstance(__uuidof(Connection));
if( SUCCEEDED(m_hresult) )
{
// Open a designated connection
m_hresult = m_pConnection->Open(connectionstring, "", "",
adConnectUnspecified);
if( SUCCEEDED(m_hresult) )
{
m_pConnection->CursorLocation = adUseClient;
return SUCCESS;
}
}
}
catch(_com_error &e)
{
m_hresult = -1;
DoError(e);
}
return FAIL;
}

SCCS LoadRecords( ... )
{
if( IsOpen() )
{
// Format a SQL string to query the records with
CString sql;
/* here goes the code to set up the SQL string */

ThreadLock();
try
{
// Open a recordset with the made up SQL string
COleVariant RecordsAffected;
_RecordsetPtr set;
set = m_pConnection->Execute( sql.AllocSysString(),
RecordsAffected, adCmdText );
// Check if anything in recordset
if( !(set->EndOfFile && set->BOF) )
{
// Get to first record
set->MoveFirst();
while( !set->EndOfFile )
{
/* put the retrieved records into memory objects, here */
/* I'm pretty darn quite sure that this isn't where the
problem is */
}
}
set->Close();
set = 0;
ThreadUnlock();
return SUCCESS;
}
catch(_com_error &e)
{
DoError(e);
}
ThreadUnlock();
}
return FAIL;
}

#define SCCS BOOL
#define SUCCESS FALSE
#define FAIL TRUE

This code isn't ready to compile for you, guys, but it is semantically
exactly what I am doing.

Where is my memory going??? *cry* My application is one that has to run for
days... well, actually "forever", but I'd settle for "for days" now. What
happens now is that it'll be using soooo much memory after a day that
restarting the application is just plain wise. (That really solves the
problem, so it looks that at that time all this memory is actually
released.)

I would be sooo grateful for you helping me out on this, as this has been
driving me up the walls for days now!!!

All the best,


Sander Verhagen
[ (e-mail address removed) ]
 
R

Roy Fine

Jeff
Without doing all the troubleshooting. the most common problem is not
calling the destructor of the instance.
you do close the recordset but setting "set" to 0. only seems to me to
change your recordptr to 0 not call the destructor.

1) since it is allocated on the stack and not on the heap, the destructor
will be called by the runtime when it goes out of scope.

2) setting a smart pointer to 0 will call Release on the Recordset interface
(have a look at the implelentation of smartpointers)

regards
roy fine
 
R

Roy Fine

Marshall Smith said:
I'm having a little trouble making out your code, but it doesn't look like
you ever release the memory used by the recordset object. If you start the
procedure with:

Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim cmd as ADODB.Command
Dim prm as ADODB.Parameter

then make sure you end it with:

Set cnn = Nothing
Set rs = Nothing
Set cmd = Nothing
Set prm = Nothing

And, make sure that it will hit those Set statements even if it runs into an
error. That will release all of the memory being used by those objects.

This is a C++ app using smartpointers - setting the objects to null serves
no useful purpose. It appears that the OP is using the connection object
through multiples calls to LoadRecords, so setting the connection ptr to
null is a very bad thing...

regards
roy fine
 
R

Roy Fine

Sander,

I have distilled the relevant parts of your application down to the code
below. Here are the coments that I would have:

1) why are you setting the default cursor location of the connection to
adUseclient? For the most part you are fetching and loading data into a
local store - there is no reason to ALSO populate a client side cursor.

2) The AllocSysString method of CString does exactly what the SysAllocString
API call does - create a BSTR and copies the data to it. You must call the
SysFreeString method somewhere. Better yet, dispense with the CString here
and use the COM Automation helper class - the _bstr_t. It has cleanup and
resource management in the class destructor.

3) The RecordsAffected parameter is net set during a SQL Select statement -
only during a DML statement, Better to just send in a NULL

4) Why not build the Recorset the old-fashioned way -set the command tst,
set the cursor location, set the connection reference, then call Open
method?

5) I didn't see anything obvious with the code that would indicate a memory
leak. You may want to have another look at the code that builds the memory
structure.


regards
roy fine


/* ****************************** */
#define SCCS BOOL
#define SUCCESS FALSE
#define FAIL TRUE

/* ****************************** */
SCCS Open(LPCSTR connectionstring) {
::CoInitialize(NULL);
m_pConnection.CreateInstance(__uuidof(Connection));
m_pConnection->Open(connectionstring, "", "",adConnectUnspecified);
m_pConnection->CursorLocation = adUseClient;
return SUCCESS;
}

/* ****************************** */
SCCS LoadRecords( ... ){
CString sql;
COleVariant RecordsAffected;
_RecordsetPtr set = m_pConnection->Execute(
sql.AllocSysString(),RecordsAffected, adCmdText );
while( !set->EndOfFile ) {
/* put the retrieved records into memory objects, here */
/* I'm pretty darn quite sure that this isn't where the problem is
*/
}
set->Close();
set = 0;
return SUCCESS;
}
 
S

Sander Verhagen

Hi,


I think the whole point is one of it being so hard to determine which
statement increases the memory use and that it aren't the database functions
after all... I can't believe that I've been looking in the wrong direction,
this long.
Anyway, I think there are nevertheless some useful pointers here to improve
my code. Thanks, guys!

All the best,


Sander Verhagen
[ (e-mail address removed) ]

Sander Verhagen said:
Hi,


Using ADO I'm experiencing what I would call a memory leak. It might
formally not be one, because I have no prove that my application is not
....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top