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?

Premillennialism

I have no clue about Hibernate, but why would you generate an ID when MySQL can do it automatically? – Tallboy

#10042438 – Chubb

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.

Martinet

You're right regarding optimizers. I wasn't aware of them, while my answer was focused on the basic generators. The enhanced generators with optimizers deal with those drawbacks. – Remonaremonetize

But you do mention hi/lo: in earlier versions of Hibernate it wasn't so nicely separated from the basic id generation strategy, however it still offered the same key advantage: reducing the number of id-managing selects. – Martinet

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.)

Remonaremonetize

IDENTITY also requires an additional query per inserted entity, in order to get the generated ID. And that is not optimizable, contrary to the table strategy. – Quarters

This is true for MySQL, as asked. If your DMBS supports retrieving the ID in the insert statement, then there's no second roundtrip (e.g. MS SQL Server). There are so many differences under the hood that you have to take care of when answering... – Remonaremonetize