[hibernate-dev] Bad performance with collection fetching and @BatchSize (HHH-9592)

[Emond Papegaaij]

Hi all,

For the past few weeks, we've been struggling with bad performance caused by 
HHH-9592. In short, even with @BatchSize(size=100) on all entities, the vast 
majority of queries (up to 70%) is in the form of 'SELECT a, b, c FROM foo 
WHERE id=123'. As far as I can see, almost all of these queries are caused by 
PersistentBag.initializeFromCache.

This method assumes an entity can be loaded from cache if the collection that 
contains it, was loaded from cache. However, as entities and collections do 
not live in the cache together, this often is not the case. The current 
implementation triggers a separate query for every record that cannot be 
loaded from the cache, ignoring the @BatchSize on that entity.

As this issue is effecting of our application severely, I'm investigating 
possible solutions. Pre-fetching entities that are likely to be fetched later 
on in the request, in a separate query helps a bit, but requires a lot of 
work. So, I'm looking for a more fundamental solution. In the ideal world, 
Hibernate would fetch a full batch (100 in our case) at once, not only from 
the collection being loaded, but also from other collections with unloaded 
entities. Is this possible? If not, what can we do to improve the situation?

Best regards,
Emond Papegaaij