Oracle's OO4O (Ole objects) works just fine in VB and is much more efficient
especially you use parameters and keep the cursor around. Just change the
parameter and reexecute. The syntax is very straightforward. Also supports
connection pooling.
No, I don't think you can have an OCI session from an ADO connection. (not
sure what that would buy you)
Jim
"Drazen Zoric" <drazen.zoric_at_vip.hr> wrote in message
news:9t02h2$nr5$1_at_fstgss02.tu-graz.ac.at...
> Ok adLockBatchOptimistic explains why all this is sent over net.
>
> Is there any way to use BatchUpdate and not to use adLockBatchOptimistic?
> Is there any other way to increase perf and use ADO/OLEDB?
>
> I will also answer replay from Niall.
> I must use ADO because we must work with single connection and it is
> made in server written in VB.
> Is there any way to get OCI session (connetion) from ADO connection?
>
>
>
> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
> news:jCuI7.25496$XJ4.15806021_at_news1.sttln1.wa.home.com...
> > > pRS->LockType = adLockBatchOptimistic;
> > means send all the prior values in the where clause to make sure no one
> has
> > changed the record under you. So that's why you are seeing so much data.
> > Hopefully you aren't opening a connection to the db every time you issue
a
> > sql statement, that would slow things down. In OCI you can do an array
> > update which would send all the data for all 50 records at once. OCI
will
> > be faster if written properly.
> > Jim
> >
> >
> > "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
> > > > !?).
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Thu Nov 15 2001 - 08:03:13 CST
