Re: Oracle and OLEDb (ADO)

From: Drazen Zoric <drazen.zoric_at_vip.hr>
Date: Wed, 14 Nov 2001 10:48:03 +0100
Message-ID: <9stekf$k9r$1@fstgss02.tu-graz.ac.at>

_ConnectionPtr pConn;
_CommandPtr pCmd;
_RecordsetPtr pRS;

pConn.CreateInstance(__uuidof(Connection)); pCmd.CreateInstance(__uuidof(Command));
pRS.CreateInstance(__uuidof(Recordset)); pC->Open(...);
pCmd->ActiveConnection = pConn;
pCmd->CommandText = "SELECT ID, NAME, NUMBER.... FROM TABLE";

pRS->CursorLocation = adUseClient;
pRS->CursorType = adOpenDynamic;
pRS->LockType = adLockBatchOptimistic;
pRS->CacheSize = 10;
pRS->Open((IUnknown*)pCmd, vtMissing, adOpenUnspecified, adLockUnspecified,

adCmdText);
// now I do some updates, lets say on 50 recs pRS->Fields->Item[2]->Value = xxx; // change NUMBER pRS->Update();
.......
pRS->UpdateBatch(adAffectAllChapters);

When I monitor network traffic during UpdateBatch() update statements are send in form:
UPDATE TABLE SET NUMBER= :1 WHERE ROWID= :2 AND ID = :3 AND NAME = :4 AND NUMBER= :5
If NAME is 200 chars than it will transfer all this 200 chars to server !! ROWID will uniquely identify every record. Why than all other fields are send as parameters?

Also, UpdateBatch take to much time. At average it takes from 100 - 200mS per update (100Mbs network)!!!

As seen You work for Oracle.
Does OCI support some direct recordset update (like one used in ADO above) or I must create UPDATE statement(s)?

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3bf12715$0$227$ed9e5944_at_reading.news.pipex.net...
> some example code would help.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission Uk
> "Drazen Zoric" <drazen.zoric_at_vip.hr> wrote in message
> news:9sqttf$eh5$1_at_fstgss02.tu-graz.ac.at...
> > Is there any way to increase update speed?
> >
> > I am already using UpdateBatch but it is still very slow.
> > I looked at network traffic and found out that Oracles OLEDB for every
> > update creates UPDATE statement and passes (via parameters) a lot of
> fields
> > used in WHERE clause (table with, lets say, INT and VARCHAR on change in
> INT
> > field will create UPDATE .... WHERE ROWID=:1 AND INT=:2 AND VARCHAR=:3
> !?).
> >
> >
>
>
Received on Wed Nov 14 2001 - 03:48:03 CST