Re: slow performance with sequences

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:<1099999772.138376.26360_at_z14g2000cwz.googlegroups.com>...
> john b wrote:
>
> > sequences that I'm having. In essence when I do a mass insert
> > (525,000 records - it's a very narrow table) into a table without a
> > sequence with a trigger the insert happens in two seconds; when I do
> > the same with the trigger on and sequence used it takes 1 minute and
> 6
> > seconds. The table does not have any indexes. I cached the sequence
>
> Lose the trigger and use the tmp_seq.nextval directly in
> populating the table.
>
> Like Mark said: there are a coupla problems with
> referencing the nextval of a sequence, one of them
> is that you can't use it directly inside PL/SQL
> without the silly DUAL table.

OK - I did the following:

1. I dropped the trigger
2. issued the following

SQL> insert into tmp (x,y) select tmp_seq.nextval , y from tmp1   2 /

524288 rows created.

Elapsed: 00:00:15.03

SQL> select x,y from tmp
  2 where rownum < 10
  3 /

         X Y
---------- ----------

   2097153          1
   2097154          1
   2097155          1
   2097156          1
   2097157          1
   2097158          1
   2097159          1
   2097160          1
   2097161          1

9 rows selected.

Elapsed: 00:00:00.00

Then I did the following:

SQL> drop sequence tmp_seq
  2 /

Sequence dropped.

Elapsed: 00:00:00.00
SQL> create sequence tmp_seq cache 100000   2 /

Sequence created.

Elapsed: 00:00:00.00
SQL> insert into tmp (x,y) select tmp_seq.nextval , y from tmp1   2 /

524288 rows created.

Elapsed: 00:00:03.00

So I droppped from 1 minute to 15 seconds, and then after I cached a big ol' bucket of sequences (100000) I got it down to 3 seconds; better, but what a pain - this means I really have to ensure that the insert for that big table are different than all the smaller tables. I was hoping to avoid that as the developers are primarily sql server literate and understand/like/work with SQL Server identities. Oh well...

JB Received on Tue Nov 09 2004 - 11:25:20 CST