Hallo Woody,

> Since you control all the access, why not create an ID table that holds the
> next ID for each table instead of relying on the speed of MAX? It's no more
> dangerous in a multi-user environment and as a matter of fact, given the
> proper attention to locking, is much safer and easier to deal with. I do
> this with many of my relations and have never had any problems with multiple
> users. It also eliminates any speed issues no matter which database you use.
> :)
>

First I did exactly that. But doing some benchmarks on mass inserts, I figured out, that the bottleneck is the number of SQL statements per second. If you do a mass insert, you would need twice the number of statements, one for read/increase ID table, one for the insert, what means half the throughput. Besides, that also true for generators, since you need a statement to read the value.

I now use a centralized synchronized method, that reads the max values on startup, and then simply counts up the IDs on each call with no further database access. This works since years without any problem. Just did not know, that max() takes that long on firebird with my ascending indices, which slows down the application startup time.

Thanks

Marc