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

bugbear apparently said,on my timestamp of 27/06/2005 7:48 PM:

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

Have you tried using array inserts? This is where your JDBC bind variables are vectors of column values. A vector dimension of 10 is usually enough for a major improvement.

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

Like Holger suggested: use
INSERT INTO TABNAME (...,table_sequence.nextval,...) It won't be ground-breaking, but it helps.

> (the table has the "obvious" constraints,
> key column is unique, primary etc)

Which means there is a unique index behind the PK column(s), so don't expect speed miracles: every new row is checked for uniqueness via the index.

> Has anyone else seen this - a factor of 15 degradation
> is "unfortunate". Diagnoses (or cures) gratefully accepted!

Other than the above and checking for the obvious things like speed of net connection, I can't think of any. Perhaps turn trace on? Check out Julian's site for the details:
http://www.juliandyke.com/

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
wizofoz2k_at_yahoo.com.au.nospam