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
