Hibernate Avoiding n+1 queries - second level associations

Question

I have a main table T1 which has a one-to-many relation with table T2 which inturn has a one-to-one relation with table T3.

When I try to fetch the data from all the tables in my query using LEFT JOIN FETCH, I am getting "illegal attempt to dereference collection" for T2 which inturn is a collection set.

I am have to get data from all the tables in one query because if I let Hibernate take care of it, I am ending with countless queries and inturn severe performance overhead.

Any advice is appreciated.

Answer 1

Not directly answer to your question: to avoid "endless queries" you should make use of lazy loading, if you don't already.

Left join fetch could sometimes make performance much better, but only if there is a single collection. If you have for instance three collections with only twenty items each, you'll get 4000 items back from your query.

You should probably post some code about your query and mapping.

Answer 2

As Stefan said, it's hard to answer your question without seeing your mappings and query. However, "llegal attempt to dereference collection" is usually caused by query trying to reference collection element properties directly. In other words, something like

from T1 as t1 where t1.t2set.prop1 = 1

would cause the above error, whereas

from T1 as t1 left join fetch t1.t2set as t2 where t2.prop1 = 1

would not. In your case if you want to get T1, T2 and T3 back as a single select you'll need to write something like

from T1 as t1
  left join fetch t1.t2set as t2
  left join fetch t2.t3
  where t1.prop = :value

Keep in mind that above select may return multiple instances of T1 (e.g. if you have 3 T1s satisfying your conditions with 4 T2 linked to each you'll get back 3*4 = 12 records). You'll need to wrap them in a Set to eliminate duplicates.

As far as "countless" queries go, the best approach depends heavily on your specific circumstances. If you map T2->T3 association as eager, you'll get N+1 selects when attempting to retrieve given T1 for the first time. Afterwards, depending on whether you use the same session / cache / etc... you may get 2, 1 or even 0 selects. OTOH, anything retrieved via fetch query like above can only be cached via query means which in most cases won't help.

Answer 3

I did try the solution provided by you.Here is how it goes..

Table T1 relates to T2 as one-to-many Table T2 relates to T3 as one-to-one

select t1 from T1obj as t1 left join fetch t1.t2set as t2 left join fetch t2.t3obj where t1.prop=t2.t1.prop

• This gives a "query specified join fetching, but the owner of the fetched association was not present in the select list" error.

  I have to use "select t1" as I am assigning the resultset to a list. If I remove the "select t1" the query would run to fetch duplicate records on t1 (say 3*4), but then I need the select because of the assignment that follows.

  Can you please guide me better on the removal of duplicates part.

  Any advice is appreciated.