Re: slow performance with sequences

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:clWjd.39009$jo2.35976_at_twister.socal.rr.com...
| 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
|

Likely your trigger has SELECT xxx.NEXTVAL INTO :NEW.ID FROM DUAL -- that's the unfortunate bit of recursion Richard is talking about.

Oracle unfortunately has some odd restrictions on referencing sequences -- it would certainly be nice to reference them as default values and just be done with it instead of needing the SELECT ... FROM DUAL trigger code. As it stands, referencing it in the VALUES clause of an INSERT or in a subquery SELECT are among your best performance options

++ mcs Received on Mon Nov 08 2004 - 21:04:23 CST