JPA/Hibernate 提高批量插入性能 [英] JPA/Hibernate improve batch insert performance

问题描述

我有一个数据模型，它在一个实体和其他 11 个实体之间存在一对多的关系.这12个实体共同代表一个数据包.我遇到的问题与这些关系的多"侧发生的插入次数有关.其中一些可以有多达 100 个单独的值，因此要在数据库中保存一个完整的数据包，它需要多达 500 次插入.

I have a data model that has a ONE TO MANY relationship between ONE entity and 11 other entities. These 12 entities together represent one data packet. The problem I am having is to do with the number of inserts that occur on the 'many' side of these relationships. Some of them can have as many as 100 individual values so to save one whole data packet in the database it requires up to 500 inserts.

我将 MySQL 5.5 与 InnoDB 表一起使用.现在，通过对数据库的测试，我发现在处理批量插入时，它可以轻松地每秒执行 15000 次插入(使用 LOAD DATA 甚至更多，但这在这种情况下不实用).

I am using MySQL 5.5 with InnoDB tables. Now, from testing the database I see that it can easily do 15000 inserts per second when processing a batch insert (and even more with LOAD DATA, but that's not practical for this case).

是否有某种方法可以使用 Hibernate 将这些单独的 500 个插入组合成例如 - 5 个具有 100 个值的插入(对于每个具有 100 个值的 5 个链接实体)?

Is there some way to bunch up these individual 500 inserts into, say - 5 inserts with 100 VALUES (for the 5 linked entities that each has 100 values) using Hibernate?

根据要求:

@OneToMany(mappedBy="beat", cascade=CascadeType.ALL)
@OrderBy("miliseconds ASC")
public List<AmbientLight> lights;

我可能还应该提到一条重要的信息 - 我正在使用 Play！框架 1.2.3

I should probably also mention one important piece of information - I am using Play! Framework 1.2.3

推荐答案

我已经通过为每个组"插入使用 Hibernate Sessions 设法解决了这个问题.结果是保存数据所需的时间减少了大约 7 倍.过去保存一个数据包"大约需要 2000 毫秒，而现在做同样的事情需要 200 到 300 毫秒.

I have managed to solve this problem by using Hibernate Sessions for each 'group' of inserts. The results are about a 7-fold reduction in time needed to save the data. Used to take approximately 2000ms to save one 'packet' and now it takes between 200ms and 300ms to do the same thing.

重复一遍——这对播放有效！框架 1.2.3 - 我不确定这是否或如何适用于使用 Hibernate 的其他框架或应用程序.

Just to repeat - this is valid for Play! Framework 1.2.3 - I am not sure whether, or how this applies to other frameworks or applications that utilize Hibernate.

    Session mySession = (Session) Pressure.em().getDelegate();

    for(int i = 0 ; i < data.size() ; i++){
        initializeFromJsonAndSave(data.get(i), mySession);
    }
    s.flush();
    s.clear();

'initializeFromJsonAndSave' 方法已更改，不再调用对象的save() 方法，而是调用mySession.save(myNewObject).

The 'initializeFromJsonAndSave' method was changed so that, instead of calling the object's save() method, calls mySession.save(myNewObject).

这篇关于JPA/Hibernate 提高批量插入性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！