Hibernate collection fetching optimally

Question

I have the following structure

Store
Rebate
RebateMetadata
RebateCommission

So, the relation is like this -

Store -> Rebate is a one to many relation Rebate -> RebateMetadata is a one-to-one mapping Rebate -> RebateCommission is a one-to-one mapping

My Query is to load all stores. And with it, load all Rebates and metadatas and commissions.

The HQL I am using is:

Select store from Store as store;

I am expecting the whole graph to be loaded in as less SQLs as possible. To prevent the n+1 selects issue, I use subselect fetching between Store->Rebate.

However, to fetch RebateMetadata and RebateCommission, I see multiple individual selects(with joins) being fired. What should I do to minimize this?

Moreover, I have the 2nd level cache turned ON, but QueryCache turned OFF.

Answer 1

You could try a fetch join. It usually takes me a while to get it right, try something like:

select store from Store as store 
 left join fetch store.rebate rebate
 inner join fetch rebate.metadata 
 inner join fetch rebate.commission

edit: updated in accordance with the second to last example I linked in the hibernate docs. This has more of a chance of being correct.

That probably won't work but it's the general idea. Alternatively you could try using batch-size (annotation @BatchSize) on the property to make sure that properties like this are loaded, say, 50 items at a time (using a SELECT...IN query).