Re: ADO record update


"Neil B" <NeilB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:193F8434-CEB3-4D1A-8859-DA68A26654C3@xxxxxxxxxxxxxxxx
Here are the methods from a compiled run that I made which has the
problem.

The primary method is immediately below (it's called
"UpdateAccountBalances2").

The suspect line of code that causes the performance problem is in it, and
is "pAccountRs->UpdateRecord2();" at the end of the "for" loop.

Let me know if there is any other code segments that would be needed.

Thanks for you help,
Neil

From what I read in your code, I have to assume that:

a.) CRecordSet is a class of your own design, not the MFC class based on
ODBC?
b.) m_adoRecordsetPtr is a member of CRecordSet, of type _RecordsetPtr?

Without trying to pretend to understand your code, I have to ask:

a.) If you are trying to update a record in a recordset, why don't you just
call m_adoRecordsetPtr->Update?
b.) How/why is data binding so integrally involved in what seems like a
back-end calc operation?


-Mark





//*******************************************************************
void CReporterView::UpdateAccountBalances2(int mode, const CString&
toDate,
const CString& fromDate)
//*******************************************************************
{
CString sql;
bool valid;
CNamesMap *pNamesMap=theApp.GetNamesMapPtr();
CAccountParms *pParms;
CAccountData *pAccountDs;
CString account, account2;
int count=0;
CBcd total, amount;

//************** Open the dataset *********************
sql= "SELECT * FROM [Account Data] WHERE (Type = 'I.' Or Type = 'S.' Or
Type = 'SM' Or Type = 'IR') ORDER BY Date, Item, Id";
CRecordSet* pAccountRs=bDB.OpenRecordSet2("AccountBalances",
"AccountDataTable", sql);
CAccountDataBindingADO* pRecord=pAccountRs->m_pAccountDataTable;

// Change all data records to new name
for (valid=pAccountRs->MoveFirst(); valid; valid=pAccountRs->MoveNext())
{
//****** Balance primary account (ACCOUNT1) - if in account map *****
account=pRecord->m_account;

if (pNamesMap->IsInMap(account))
{
pParms=pNamesMap->GetParms(account);
total=pParms->m_balance;

amount.Parse(pRecord->m_amount);
total+=amount;

// Put new total in the account map - if not a transaction header record
// if (!pAccountDs->m_account2.IsEmpty()) pParms->m_balance=total;
pParms->m_balance=total;

// Put new total in the primary account balance field of the record
strcpy(pRecord->m_accountBalance, total.Format());

pAccountRs->UpdateRecord2();
}

count++;

}
TRACE("\n%5i Account1 = %30s, Account2 = %s", count, account, account2);

// Close the recordset
bDB.CloseRecordSet(pAccountRs);
}

//*************************************************
void CRecordSet::UpdateRecord2()
//*************************************************
{
TESTHR(m_adoBindPtr->Update(m_pAccountDataTable));
}

//*************************************************
//AccountDataBindingADO.h
//*************************************************
#pragma once
#include "icrsint.h"
#include "defines.h"

class CAccountDataBindingADO :
public CADORecordBinding
{
public:
CAccountDataBindingADO(void);
virtual ~CAccountDataBindingADO(void);
void operator = (CAccountDataBindingADO *pParms);
void operator = (CRecord& record);

// ******** Be sure to uncomment the binding of CAccountDataBindingADO
// ******** in method bool CRecordSet::BindToDbFields()

BEGIN_ADO_BINDING(CAccountDataBindingADO)
ADO_FIXED_LENGTH_ENTRY2 (1, adInteger, m_id, false)
ADO_VARIABLE_LENGTH_ENTRY4 (2, adVarChar, m_item, sizeof(m_item), true)
ADO_VARIABLE_LENGTH_ENTRY4 (3, adVarChar, m_type, sizeof(m_type), true)
ADO_FIXED_LENGTH_ENTRY2 (4, adDate, m_date, true)
ADO_VARIABLE_LENGTH_ENTRY4 (5,
adVarChar, m_description, sizeof(m_description), true)
ADO_VARIABLE_LENGTH_ENTRY4 (6, adVarChar, m_amount, sizeof(m_amount),
true)
ADO_VARIABLE_LENGTH_ENTRY4 (7, adVarChar, m_memo, sizeof(m_memo), true)
ADO_FIXED_LENGTH_ENTRY2 (8, adDate, m_reviewed, true)
ADO_VARIABLE_LENGTH_ENTRY4 (9,
adVarChar, m_account, sizeof(m_account), true)
ADO_VARIABLE_LENGTH_ENTRY4 (10,
adVarChar, m_accountBalance,sizeof(m_accountBalance),true)
ADO_VARIABLE_LENGTH_ENTRY4 (11,
adVarChar, m_reference, sizeof(m_reference), true)
ADO_FIXED_LENGTH_ENTRY2 (12, adDate, m_ok, true)
ADO_VARIABLE_LENGTH_ENTRY4 (13,
adVarChar, m_account2, sizeof(m_account2), true)
ADO_VARIABLE_LENGTH_ENTRY4 (14,
adVarChar, m_account2Balance,sizeof(m_account2Balance),true)
ADO_VARIABLE_LENGTH_ENTRY4 (15,
adVarChar, m_reference2, sizeof(m_reference2), true)
ADO_FIXED_LENGTH_ENTRY2 (16, adDate, m_ok2, true)
ADO_VARIABLE_LENGTH_ENTRY4 (17,
adVarChar, m_project, sizeof(m_project), true)
ADO_VARIABLE_LENGTH_ENTRY4 (18,
adVarChar, m_project2, sizeof(m_project2), true)
ADO_VARIABLE_LENGTH_ENTRY4 (19,
adVarChar, m_projectBalance,sizeof(m_projectBalance),true)

END_ADO_BINDING()

public:
// Fields to be Bound
long m_id;
char m_item[12]; // + 1 for NULL
char m_type[5]; // + 1 for NULL
COleDateTime m_date;
char m_description[81]; // + 1 for NULL
char m_amount[21]; // + 1 for NULL
char m_memo[1024]; // + 1 for NULL
COleDateTime m_reviewed;
char m_account[31]; // + 1 for NULL
char m_accountBalance[21]; // + 1 for NULL
char m_reference[21]; // + 1 for NULL
COleDateTime m_ok;
char m_account2[31]; // + 1 for NULL
char m_account2Balance[21]; // + 1 for NULL
char m_reference2[21]; // + 1 for NULL
COleDateTime m_ok2;
char m_project[31]; // + 1 for NULL
char m_project2[31]; // + 1 for NULL
char m_projectBalance[21]; // + 1 for NULL


};

//*************************************************
CRecordSet* CDataBase::OpenRecordSet2(CString name, CString type, CString
sql)
// Return: NULL pointer - if open fails
//*************************************************
{
HRESULT hr;
_bstr_t errorMsg;
_RecordsetPtr pRecordset;
CRecordSet *pNewRecordset=NULL;
void* pVoid;

// Create a record set object
hr = pRecordset.CreateInstance(__uuidof(Recordset));

// Select records
try {hr=pRecordset->Open(LPCTSTR(sql), m_connection.GetInterfacePtr(),
adOpenKeyset, adLockOptimistic, adCmdText);}
catch(_com_error &e)
{
// Handle errors
errorMsg=e.Description();

// AfxMessageBox(e.ErrorInfo);
return pNewRecordset;
}

// Create RecordSet class and attach the ADO recordset pointer to it
pNewRecordset = new CRecordSet(name, type, pRecordset, sql);

// Bind the record set to the DB fields (ADO recordset, parm #3, must be
open first)
pNewRecordset->BindToDbFields2();

return pNewRecordset;

}

//**********************************************************
bool CRecordSet::BindToDbFields2()
//**********************************************************
{
CString type;
_bstr_t errorMsg;

// Add record to NAMES TABLE
try
{
// Create and attach the bind pointer
// IADORecordBindingPtr m_adoBindPtr(m_adoRecordsetPtr);
TESTHR(m_adoRecordsetPtr->QueryInterface(__uuidof(IADORecordBinding),(LPVOID*)&m_adoBindPtr));


// Bind the ACCOUNT DATA table
m_pAccountDataTable=new CAccountDataBindingADO(); //**User Table
TESTHR(m_adoBindPtr->BindToRecordset(m_pAccountDataTable)); //**User Table
}

catch(_com_error &e)
{
// Handle errors
errorMsg=e.Description();
ASSERT(false);
}

// Successful binding return
return true;
}




"Stephen Howe" wrote:

I'm having a performance problem with the following line of code:

TESTHR(m_adoBindPtr->Update(m_pAccountDataTable));

It's in a loop that runs in a fraction of a second without it and runs
in
30
seconds with it included. The loop executes this line about 3000 times.
The
only field changed is a text field of 20 character max. The loop
includes
the
binding of a new record on each pass...

Why?
I bind records once and then reuse the same struct/class over and over
again.
Seems nonsensical to keep binding on per-loop-iteration.

...and interogates 2 of the fields.

Below are some selected lines from other areas of the code to help
identify
the base classes etc. :

IADORecordBindingPtr m_adoBindPtr;
inline void TESTHR(HRESULT _hr) { if FAILED(_hr)
_com_issue_error(_hr); }
class CAccountDataBindingADO : public CADORecordBinding

Doesnt mean anything. You might have we as said

int i;
:
:
for (; ; ++i) // <= this increment is slow, why?

I suggest you give a _COMPLETE_ compileable example that is slow and then
there is something to examine.
Merely mentioning a few lines of your code is not enough to narrow things
down.
(and I tend to think that those programmers who display just 2 lines of
code
almost like fish entrails and have the incredible expectations that you
will
be able to diagnose _EXACTLY_ what is wrong from just those 2 lines -
well
they cant be the sharpest knife in the drawer).

Any ideas why the "Update" method takes so long to execute???

No. You have probably done something incorrect but it will be the code
you
dont mention.

Cheers

Stephen Howe





.