Using seq.nextval without using user-defined sql

vpauls · Post by **vpauls** » Mon Oct 29, 2007 7:07 am

Is it possible to set a column as seq.nextval without using user-defined sql?

I have done this using user-defined sql but it makes the server job a lot more cumbersome to modify.

chulett · Post by **chulett** » Mon Oct 29, 2007 7:23 am

Only by putting the 'nextval' select into a lookup, from what I've seen.

vpauls · Post by **vpauls** » Mon Oct 29, 2007 7:40 am

chulett wrote:Only by putting the 'nextval' select into a lookup, from what I've seen.

I have played around with that, but it is extremely slow.

A minor speed test using 1000 000 input rows. Showed that
- user defined sql had 20000 rows/sec
- using a DB trigger to insert nextval about 9000 rows/sec
- using lookup only for nextval had 500-1000 rows/sec
- looking up nextval together with source data 5-6000 rows a second.

The source table consisted of the number 1 - > 1000 000

chulett · Post by **chulett** » Mon Oct 29, 2007 7:45 am

Never said it was speedy or a better approach.