Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle and OLEDb (ADO)

Re: Oracle and OLEDb (ADO)

From: Drazen Zoric <drazen.zoric_at_vip.hr>
Date: Thu, 15 Nov 2001 10:41:01 +0100
Message-ID: <9t02h2$nr5$1@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 - 03:41:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US