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

Home -> Community -> Usenet -> c.d.o.server -> Re: MASSIVE degradation of insert using sequence ID's via JDBC on 9i?

Re: MASSIVE degradation of insert using sequence ID's via JDBC on 9i?

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Mon, 27 Jun 2005 13:01:17 +0200
Message-ID: <d9om9u$spo$1@news.BelWue.DE>


bugbear wrote:
> Dear all;
> we are using an Oracle sequence to generate primary
> keys in our DB application. This is done in the usual

What's usual to you isn't that obvious to most people in this group. So please be more verbose.

>
> We have tested against Oracle 9i and Oracle 10g.
>
> When we run locally (i.e. application and database
> on the same machine) performance is "OK" for both
> 9 and 10, but when we run client-server against 9i, we get
> a factor of 15 speed degradation. We can only manage
> 5 insertions per second with both client and server being
> 2.5 GHz Intel boxes.
>
> We are using prepared statements via JDBC.
>
> The code simply gets the next id in the sequence
> (SELECT table_sequence.nextval INTO id FROM DUAL;)
> and uses it in the insert statement.
> The 2 statements are in the same transaction.
>
> (the table has the "obvious" constraints,
> key column is unique, primary etc)
>
> We are not doing "bulk" inserts (except in our timing tests ;-)
>
> Has anyone else seen this - a factor of 15 degradation
> is "unfortunate". Diagnoses (or cures) gratefully accepted!
>
> BugBear

You're doing this to yourself. Why the two network roundtrips when one would be sufficient?

insert into table values (table_sequence.nextval, ....)

if you need the value of the sequence look into the returning clause.

Regards
Holger Received on Mon Jun 27 2005 - 06:01:17 CDT

Original text of this message

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