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
>
> 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

Couple of quick things to check:

1. put the seq.nextval in the insert itself
2. ensure the sequence has a nice high cache value

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------