Re: slow performance with sequences

john b wrote:
> I'm curious to see if people are having the same slow performance with
> 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
> up to 10000 and that made very little difference. This occurs in both
> 8i (8.1.7.4) and 9i. For what it's worth SQL Server does this in 6
> seconds using an identity. (No I'm not trying to start a flame war -
> I just want to know if I'm doing something wrong.) The insert
> generates all of 6Mb of data, and my rollback segments consume this
> w/o any trouble. My CPU and I/O go up to 100% each time - yikes! I
> executed this test many times - and it resulted in the same
> performance every time.

<snip demo>

If you'll benchmark the difference between the trigger implementation and direct reference to the sequence during the insert, you should find that most of this overhead comes from the trigger and recursive SQL instead of the sequence. Using a trigger is certainly convenient and less error prone but it's not high performance.

--
Richard Kuhler