优化Hibernate序列号的生成 [英] optimise Hibernate Sequence ID generation

问题描述

我在尝试将Hibernate与SAP HANA内存数据库连接时遇到了一些性能问题，该数据库不支持AUTO_INCREMENT（ http://scn.sap.com/thread/3238906 ）。

因此，我设置了Hibernate使用序列来生成ID 。

  @GeneratedValue（strategy = GenerationType.SEQUENCE，generator =myseq）
 @SequenceGenerator（name =myseq ，sequenceName =MY_SEQ，allocationSize = 1）
  

但是当我插入大量记录例如40000），Hibernate首先生成ID。它看起来像：

  DEBUG Thread-1 org.hibernate.SQL  - 从DUMMY 
中选择MY_SEQ.nextval DEBUG Thread -1 org.hibernate.id.SequenceGenerator  - 生成的序列标识符：BasicHolder [java.lang.Long [92080]] 
 DEBUG Thread-1 org.hibernate.event.internal.AbstractSaveEventListener  - 生成的标识符：92080，使用策略：org.hibernate.id.SequenceHiLoGenerator 
 DEBUG Thread-1 org.hibernate.SQL  - 从DUMMY 
中选择MY_SEQ.nextval Thread-1 org.hibernate.id.SequenceGenerator  - 生成的序列标识符：BasicHolder [ java.lang.Long [92081]] 
 DEBUG Thread-1 org.hibernate.event.internal.AbstractSaveEventListener  - 生成的标识符：92081，使用策略：org.hibernate.id.SequenceHiLoGenerator 
  

只有在所有ID生成后，才会开始实际插入。

全部总共需要大约5分钟的时间来插入40000条记录（通过网络到远程数据库），这非常缓慢 - 内存数据库。我认为这是因为Hibernate会逐个选择ID的下一个值：

 发送请求到数据库
 get id 
发送下一个请求
 ... 
  

我想加快速度身份证的生成，但不幸的是，我还没有足够的了解它如何改进它。我已经搜索了可能的解决方案，并发现了以下想法：
$ b <1> 调用sequence.nextval内部插入语句。然而，Hibernate团队认为这是不可能的： https：//forum.hibernate .org / viewtopic.php？f = 1& t = 932506

$ b 2）使用SequenceHiLoGenerator 。这可能是一个解决方案，但我不明白如何设置它...如果我写的是

  @GeneratedValue（strategy = GenerationType.SEQUENCE，generator =myseq）
 @SequenceHiLoGenerator（name =myseq，sequenceName =MY_SEQ，allocationSize = 1），
  

我得到无法从SequenceHiLoGenerator转换为AnnotationEclipse中的错误

3）写入数据库触发插入。然而，对我来说，它看起来像一个糟糕的解决方案，因为我想要一个通用的Hibernate Dialect与任何数据库实例一起工作。我不知道如何在Hibernate Dialect中包含这样的触发器。

您建议使用哪种解决方案？您有任何其他想法吗？

我衷心感谢您对此问题的任何帮助。如果有人能够提供一些解决方案或文档，甚至更详细的解决方案，这将是非常好的。

非常感谢您提前。

解决方案

序列中的值是逐个获取的，因为 allocationSize 设置为1. allocationSize 的默认值为50，这已经好多了。在这种情况下，如果插入40000记录是典型的用例，那么使用高于该值的值可能是有意义的。

如果创建序列的脚本是自写的（而不是通过Hibernate）， INCREMENT BY 的值应该与 allocationSize 的值相同。

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.

解决方案

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.

这篇关于优化Hibernate序列号的生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！