Re: 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 17:13:07 +0100
Message-ID: <42c02593$0$30833$ed2619ec@ptn-nntp-reader01.plus.net>

Noons wrote:
> 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.

I would imagine so; but see my other post. While my test looks a lot like a (very!) badly coded bulk insert, our actual application is a heavily user interactive beasty. The test is intended to emulate the kind of thing our app does, in a simplified context.

To give some numbers, our actual application is getting 5 or 6 inserts per second.

The simplified harness is managing around 50 a second against a remote 9i, which sounds sort of OK until you hear that a remote 10g installed on the same machine gives us 400 a second.

Somewhere, 9i is killing us.

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

That change will complicate our application code (don't ask!) but we'll look into it. In the stand alone code, it gave some useful improvements.
But we'd still like 9i to perform a little more like 10g.

>

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

Agreed; but (banging on a bit) 10g is managing ;-)

>

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

Thanks for all the advice (and a not-forgotten dinner)

    BugBear Received on Mon Jun 27 2005 - 11:13:07 CDT