optimise Hibernate Sequence ID generation

Question

I'm facing some performance issues while trying to connect Hibernate with SAP HANA In-Memory database, which has no support for AUTO_INCREMENT (http://scn.sap.com/thread/3238906).

So I have set Hibernate to use sequences for ID generation.

  @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="myseq") 
  @SequenceGenerator(name="myseq",sequenceName="MY_SEQ",allocationSize=1)

But when I insert big number of records (e.g., 40000), Hibernate first generates IDs. It looks like:

DEBUG Thread-1 org.hibernate.SQL - select MY_SEQ.nextval from DUMMY
DEBUG Thread-1 org.hibernate.id.SequenceGenerator - Sequence identifier generated: BasicHolder[java.lang.Long[92080]]
DEBUG Thread-1 org.hibernate.event.internal.AbstractSaveEventListener - Generated identifier: 92080, using strategy: org.hibernate.id.SequenceHiLoGenerator
DEBUG Thread-1 org.hibernate.SQL - select MY_SEQ.nextval from DUMMY
DEBUG Thread-1 org.hibernate.id.SequenceGenerator - Sequence identifier generated: BasicHolder[java.lang.Long[92081]]
DEBUG Thread-1 org.hibernate.event.internal.AbstractSaveEventListener - Generated identifier: 92081, using strategy: org.hibernate.id.SequenceHiLoGenerator

And only after all IDs are generated, it starts actual inserting.

All together, it takes about 5 minutes to insert 40000 records (via network to the remote database), which is extremely slow for in-memory database. I assume that it happens because Hibernate selects next values for ID one by one:

send a request to database
get id
send next request
...

I would like to speed up the ID generation, but unfortunately, I have not enough understanding how it works to improve it. I have searched for the possible solution and found the following ideas:

1) call sequence.nextval inside insert statement. However, Hibernate Team says that it's not possible: https://forum.hibernate.org/viewtopic.php?f=1&t=932506

2) Use SequenceHiLoGenerator. This could be a solution, but I do not understand how to set up it... If I write

  @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="myseq") 
  @SequenceHiLoGenerator(name="myseq",sequenceName="MY_SEQ",allocationSize=1),

I get "cannot convert from SequenceHiLoGenerator to Annotation" Error in Eclipse

3) Write a database trigger on inserts. However, for me it looks like a bad solution because I want to have an universal Hibernate Dialect working with any database instance. And I have no understanding how to include such a trigger into Hibernate Dialect.

Which solution would you suggest? Do you have any other ideas?

I will sincerely appreciate any help with this question. It would be great if somebody can provide some solution or documentation or even just more detailed pathway to the solution.

Thank You very much in advance.

Answer 1

Values from sequence are fetched one by one, because allocationSize is set to 1. Default value for allocationSize is 50, which is already much better. In this particular case it probably makes sense to use value higher than that, if inserting 40000 record is typical use case.

If script to create sequence is self written (and not by Hibernate), value of INCREMENT BY should be same as value of allocationSize.