Hibernate, what is the most efficient id generation strategy?

Question

I need to insert many entities into the database via Hibernate. So, I want to find the most effective algorithm for Id generation.

Accordingly Hibernate Documentation exists four widely used generation strategies:

• IDENTITY
• SEQUENCE
• TABLE
• AUTO

I should use MySQL database, so I cannot apply SEQUENCE generation strategy. What about other strategies? What is the most efficient from performance point of view?

Answer 1

The best id generators in Hibernate are enhanced-table and enhanced-sequence, coupled with an appropriate optimizer, such as hilo. I have experience with enhanced-table + hilo, inserting over 10,000 records per second.

BTW the statement that "hilo needs an additional query per generated entity" is patently false: the whole point of the optimizer is to prevent this.

Answer 2

As you can't use SEQUENCE, and AUTO just automatically selects a supported generator algorithm out of the existing ones, you are left with IDENTITY and TABLE.

• TABLE: uses a hi/lo algorithm to efficiently generate identifiers of type long, short or int, given a table and column as a source of hi values. The hi/lo algorithm generates identifiers that are unique only for a particular database. -> Means an extra query per generated entity. (This is not true if you use optimizers. Unfortunately, using no optimizer generally is the default, if no optimizer was specified.)

• IDENTITY: supports identity columns in DB2, MySQL, MS SQL Server, Sybase and HypersonicSQL. -> Performance-wise, this is the way to go, the same way you would do without Hibernate normally. Database generated, almost no overhead.

There exist more Hibernate specific generators, but they won't beat performance-wise the database generated ID. (See 5.1.2.2.1. Various additional generators in your linked document.)