1

I am new to hibernate. Please correct me if any of the below is wrong.

There is an entity which is using a sequencegenerator for id generation. Because of this every insert is having two calls one to get sequence and other to do the insert

@Id
@SequenceGenerator(name="i_gen", sequenceName="b_seq", allocationSize=6)
@GeneratedValue(generator="i_gen")
private Long id;

During my research about allocationsize i understood that on specifying the allocation size, we will not call db for next few allocatedsize but simply increment by 1. But i don't see this behavior. Please correct me if i am wrong.

The following are the sql logs that i see 

[INFO ] [26082016.002159] sqltiming:357  - select b_seq.nextval from dual  {executed in 69 msec}
[INFO ] [26082016.002200] sqltiming:357  - select b_seq.nextval from dual  {executed in 174 msec}
[INFO ] [26082016.002200] sqltiming:357  - select b_seq.nextval from dual  {executed in 74 msec}
[INFO ] [26082016.002200] sqltiming:357  - select b_seq.nextval from dual  {executed in 77 msec}
[INFO ] [26082016.002200] sqltiming:357  - select b_seq.nextval from dual  {executed in 78 msec}
[INFO ] [26082016.002200] sqltiming:357  - select b_seq.nextval from dual  {executed in 161 msec}

For 6 objects insert in a single transaction, i see 6 calls to db to get nextval and 6 inserts. I optimized the inserts by using batch inserts. However i am unable to get around the seq generator issue. I see a huge performance hit because of this.

Is there anything else for this not to make so many db calls for getting seqnumber?

Anonymous7
  • 41
  • 1
  • 5
  • I cant remember the syntax but you can bulk preallocate a block of primary keys to a JVM then use them – farrellmr Aug 26 '16 at 07:42
  • Your expectation is correct. Other JPA impls that I've used don't call the sequence for each insert, instead using the allocation and caching those. I would normally expect the `@SequenceGenerator` to be specified at CLASS level (though not essential), and see strategy=GenerationType.SEQUENCE on the `@GeneratedValue` (more essential) – Neil Stockton Aug 26 '16 at 09:22

1 Answers1

1

You would have to set your DB sequence to INCREMENT BY 6. As described here The allocationSize=n means: "Go and fetch the next value from the database once in every n persist calls. And locally increment the value by 1 in between.". Also, check out this answer on SO.

Community
  • 1
  • 1
veljkost
  • 1,748
  • 21
  • 25