padraigf padraigf -4 years ago 182
C++ Question

Limiting memory-usage of ADO recordset

I'm connecting to a database using ADO (C++), I'm reading a table of about 5m records, and I want to limit the memory-usage on the client side. Currently, iterating through the records causes about a 1GB memory-increase on the process, and I want to reduce this significantly.

The code is very standard C++ ADO (edited for readability):

ADODB::_ConnectionPtr m_pConnection;
m_pConnection.CreateInstance(__uuidof(ADODB::Connection));
m_pConnection->Open(_bstr_t(strConnect), m_strDBUserName, m_strDBPassword, -1);

ADODB::_RecordsetPtr pRecordSet;
pRecordSet.CreateInstance( __uuidof(ADODB::Recordset) );

pRecordSet->putref_ActiveConnection( pConnection );
pRecordSet->Open( _variant_t(bstrQuery), vNull, ADODB::adOpenForwardOnly, ADODB::adLockOptimistic, ADODB::adCmdText );

pRecordSet->MoveFirst();
while( !pRecordSet->EOF )
{
CString cardNum = (LPCWSTR)(pRecordSet->Fields->GetItem("CARD_NUM")->Value.bstrVal);
int nSeqNum = pRecordSet->Fields->GetItem("SEQ_NUM")->Value.intVal;

// process data

pRecordSet->MoveNext();
}


What I observe is, as it iterates through the records, memory usage increase linearly, at about 200MB per million records.

What I'd like to do is: while, processing, tell the record-set to free the memory of the already-processed records, so that memory usage is kept low. Is there a function to do this?

If not, can I take a manual approach to partitioning the data with multiple record-sets, releasing them as I go?

Answer Source

My solution to this was to manually free the data. Every N records, I close the record-set, and perform a new query starting at the next record. It required some changes to my queries...

int partitionSize = 100000, nRecordIndex = 0;
CString query("select CARD_NUM, SEQ_NUM from CMS_CARD order by CARD_NUM, SEQ_NUM");
DoSelect( theCWIDatabaseManager.GetConnection(), pRecordSet, query );
pRecordSet->MoveFirst();

while( !pRecordSet->EOF )
{
    // process data

    nRecordIndex++;
    if( nRecordIndex % partitionSize == 0 )
    {
        pRecordSet->Close();
        query.Format( "select CARD_NUM, SEQ_NUM from CMS_CARD where CARD_NUM = '%s' and SEQ_NUM > '%s' union select CARD_NUM, SEQ_NUM from CMS_CARD where CARD_NUM > '%s' order by CARD_NUM, SEQ_NUM", lastCardNum, lastSeqNum, lastCardNum );
        DoSelect( theCWIDatabaseManager.GetConnection(), pRecordSet, query );
        pRecordSet->MoveFirst();
    }
}

Not as elegant as if the record-set freed the memory, but seems to work.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download