2

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.

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

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?

As Requested: 

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

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

Iv4n
  • 359
  • 1
  • 4
  • 18
  • can you post your ONE TO MANY annotation or the hbm.xml? – Angga Jul 24 '13 at 10:23
  • I've updated the original post. The issue most likely is the fact that for each LIGHT object it creates one INSERT statement (and the same for 6 other objects) so it ends up doing about 500 single inserts in one transaction. – Iv4n Jul 24 '13 at 13:40
  • I thing there should be no performance problem about that, unless you do that in so many thread. maybe you can try to set the batch size parameter? or use `.createNativeQuery()` instead if you thing sql is faster and worth it. – Angga Jul 24 '13 at 16:23
  • Yes, I am aware a native query could be faster but I am not looking forward to writing native queries for the 11 entities that are linked this way! Also, doing 500 inserts into the same table will ALWAYS be slower than one insert with 500 values. (INSERT INTO .... VALUES ( ), ( ), ( ), .... ) – Iv4n Jul 24 '13 at 20:08
  • how about the batch size parameter? at least you can save some roundtrip to the database. can you post your code to insert that entity? – Angga Jul 25 '13 at 02:24
  • I have tried the batch size parameter (including other similar suggestions to include server-side grouping of queries, etc...) but nothing seemed to make a difference. Batch size 10, 30, 50, 100 and 1000 all yielded the same results. – Iv4n Jul 25 '13 at 08:08

2 Answers2

3

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.

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();

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

Iv4n
  • 359
  • 1
  • 4
  • 18
  • This limit the overhead due to hibernate dirty checking but under the hood you don't use jdbc batch api so this is not as fast as raw jdbc, check your sql logs to see what happens – Seb Cesbron Aug 02 '13 at 08:08
  • Do you have suggestions how to further improve this without veering too far outside the typical Play! Framework constraints? – Iv4n Aug 02 '13 at 11:54
  • If the performance is ok for you you can stay with this solution. The other improvement you can have is to separate your commit into multiple transactions instead of flushing and clearing hibernate session. One easy way to do this with play is to use a Job. Your Job take for example 100 objects iterate over them and commit. In your controller you can launch several Jobs in parallel if the order does not matter and wait for all jobs to terminate in your controller – Seb Cesbron Aug 06 '13 at 08:37
1

Here are two good answers on the subject

Notice that with identity generator (it is the generator used by default with play) batch insert is disabled.

Community
  • 1
  • 1
Seb Cesbron
  • 3,823
  • 15
  • 18