Marc,

>
> Currently I'm using Derby, Oracle, Firebird and MS SQL, that's why I can easily compare the performance of this DBs in different areas.
>
> I'm using identical indices on all these databases, and none of the other shows any delays on that max() function. (Even with MS SQL and Oracle using MVCC design)
>

Right. The problem with MAX and ascending indexes is a combination of
the Firebird MVCC implementation and the Firebird index access
strategy. Firebird indexes are imprecise because the index includes
all versions of a record, not just the most recent. Oracle and MS SQL
use back versions of index pages for older transactions, which avoid
the "whoops it's there but not in your view" problem at the cost of
keeping old copies of index pages around. Firebird avoids holding
exclusive locks on sections of index when they are being split by
allowing readers to move forward, but not backward, through an index.
Yes, it might be possible to make an unqualified MAX faster by reading
to the end, finding a qualified row (Hurray!) or if not, starting at
the top and reading forward to the next lower value, and finding a
qualifiying row or if not, starting again at the top and reading, and
starting and reading and... You see the problem. Could be done,
would be faster, is a lot of code, and encourages doing something
that's better done with sequences/generators.

> In deed, I'm using the max() function to create IDs for new elements, but since this is done by a synchronized method within the middleware, it just need to be done once on the first connect. But this call for two tables needs up to 60 sec on an 3GHz i7 quadcore, with the database file on a SSD drive. This slows down the first login to the system.
>

Most of the databases you name now make some variation on sequences
available - that would be a better solution than relying on the MAX
function. The real problem with using MAX to set the basis for a
unqiue identifier is that in a concurrent environment, it's not
reliable.

Best regards,

Ann