Re: Oracle and OLEDb (ADO)

C/OCI isn't my language and so others may have a better example.

However I believe that you should be using a static cursor and adLockbatchOptimistic not unspecified when you open the recordset. Dynamic cursors havce a *lot* of overhead.

You may also get better performance by changing your oledb/odbc provider.

If you can guarantee that you will always work with oracle databases, as opposed to generic RDBMS platforms give some consideration to Orace Objects for OLE as an alternative to ADO.

Finally as you see from my signature I most definitely do not work for oracle, just with their products.

Sorry I couldn't be more use.

--
Niall Litchfield
Oracle DBA
Audit Commission UK





"Drazen Zoric" <drazen.zoric_at_vip.hr> wrote in message
news:9stekf$k9r$1_at_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

> > !?).

> > >

> > >

> >

> >

>

>