jpa performance crash after using @batchsize

Question

i have a java jpa/hibernate app that needs to get a lot of data to perform its task. I encountered the n+1 problem, so i decided to use the hibernate.default_batch_fetch_size (@batchsize) property to lower the needed sql roundtrips. I tried some values but the performance crashed with nearly all tried values. batchsize: 0 - sqls send: 14000 - duration: about 1 min batchsize: 4 - sqls send: 5000 - duration: more than 10 min batchsize: 10 - sqls send: 2700 - duration: about 5 min batchsize: 100 - sqls send: 400 - duration: about 1 min is this a "normal" behaviour? if not what can be the mistake? I logged the generated sql with log4jdbc. I noticed that between every batched statement lied about 100-150 millsecs. If i run the sql later the runtime for each statement was no more than 20 millisecond. So this doesnt seam to be a DB (IN statement) related issue. Java: 1.6.0_31, Hibernate 3.6.7, DB Postgres 9.1.1, JDBC postgresql-9.1-901.jdbc4.jar thanks in advance update to make things clear:…

Accepted Answer

after some debuging i found the issue. Hibernate (at least in version 3.6.7) stores all mapped collections in a map. You can access these map with a snipplet like this:

```
SessionImpl si = ((SessionImpl) entityManager.getDelegate());
PersistenceContext persistenceContext = si.getPersistenceContext();
persistenceContext.getCollectionEntries();
```

So every collection creates one entry in this map.If you have pojos with lots of collections like in my case this grows large fast. For example 10.000 pojos loaded with each 32 collections you have 320.000 Collection entries. Hibernate now just iterates through the map (org.hibernate.engine.BatchFetchQueue.getCollectionBatch(CollectionPersister, Serializable, int, EntityMode)) to find not loaded Collection ids to put these later in the IN clause. Hibernate does not limit the search for the key to a certain typ of collection so this gets even worse.

I guess i have to clean up some collections and hope hibernate gets a more efficent way to find the keys in higher versions.

Update: this comment on hibernate jira might be interesting for someone with the same problem: https://hibernate.onjira.com/browse/HHH-1775?focusedCommentId=42686&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-42686

Update: this issue was adressed in hibernate version: 4.1.8

jpa performance crash after using @batchsize

Solution

Problem