Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: MASSIVE degradation of insert using sequence ID's via JDBC on 9i?
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