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

From: bugbear <bugbear_at_trim_papermule.co.uk_trim>
Date: Mon, 27 Jun 2005 10:48:07 +0100
Message-ID: <42bfcb58$0$30839$ed2619ec@ptn-nntp-reader01.plus.net>

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 Received on Mon Jun 27 2005 - 04:48:07 CDT