Re: performance tuning question

How about bind variables and the other suggestions people made? I think the one where you do the insert and if it fails due to duplicate then do the update. Probably isn't using the index because you haven't analyzed it. Probably best thing is to wrap it into a stored procedure and use the array interface to do a bulk of them at a time. What is the explain plan on the select?
Jim
"Vlad G" <vladgri_at_NO_hotmail_SPAM.com> wrote in message news:eNLA8.6972$M7.2923477_at_twister.socal.rr.com...
> I tried to replace select * with select 1, got some marginal improvement,
 if
> any at all. I don't think this is the problem..
>
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> news:0qpA8.11463$xv1.421_at_rwcrnsc51.ops.asp.att.net...
> > You need to use bind variables. Read the manual on that. Also just
 select
> > 1 not * .
> > That will help a lot.
> > Jim
> > "Vlad G" <vladgri_at_NO_hotmail_SPAM.com> wrote in message
> > news:gbpA8.4450$M7.1797776_at_twister.socal.rr.com...
> > > Hi there,
> > >
> > > I'm looking for an advice what configuration parameters I need to look
 at
> > > first to boost Oracle 8i performance for the following simple
 scenario.
 I'm
> > > doing a batch update of a table containing about 65k records. The
 table
> > > structure is rather simple:
> > >
> > > MY_TABLE: ID, COL1, COL2, COL3
> > >
> > > all fields are integers except COL3 which is VARCHAR 2000. There is no
> > > primary key set on the table during update. The program is Java and
 its
> > > logic is (in pseudocode):
> > >
> > > for (each id in update)
> > > {
> > > if (exists(SELECT * FROM MY_TABLE WHERE ID=id))
> > > UPDATE MY_TABLE SET COL1=val1, COL2=val2, COL3=val3 WHERE
 ID=id
> > > else
> > > INSERT INTO MY_TABLE id,val1,val2,val3
> > > }
> > >
> > > The update is going painfully slow and takes about 6 hours, and as the
> > > matter of fact it is about 7 times slower than MS SQL or DB2 on both
 AIX
 and
> > > Win2k (can't really tell about MS SQL on AIX ...). This is not a
 network
> > > problem or other environment related issue since load into empty table
 using
> > > only inserts without selects is pretty fast and takes 10-15 minutes,
 so
 this
> > > is full table scans that slow things down. I get 20% performance gain
 if
> > > index is created on ID field which doesn't help a lot. So, what do I
 need
 to
> > > look at first? buffers? shared pool? block size? do I need to supply
 any
> > > optimizer hints in my query so the index is actually used? Just want
 to
 get
> > > some info before I start poking around...
> > >
> > > Thanks for any clues.
> > > Vladimir.
> > >
> > >
> >
> >
>
>
Received on Sat May 04 2002 - 17:11:41 CEST