Hibernate join fetch does an N+1, how to fix it?

Question

I have this query:

@NamedQuery(
        name = "org.mygovscot.stars.model.UserNeed.findAll", query =
            "SELECT un FROM UserNeed un " +
            "LEFT JOIN FETCH un.services "
    )

With this mapping from Service to UserNeed:

<set name="userNeeds" table="service_userNeed">
  <key column="service_id"/>
  <many-to-many column="userNeed_id" class="UserNeed"/>
</set>

and this mapping from UserNeed to Service:

<set name="services" table="service_userNeed">
  <key column="userNeed_id"/>
  <many-to-many column="service_id" class="Service"/>
</set>

That is, its a bi-directional many-to-many.

I query it like this:

currentSession()
    .getNamedQuery("org.mygovscot.stars.model.UserNeed.findAll")
    .list();

My understanding was that a "join fetch" would eagerly fetch associations, thereby avoiding doing an N+1 query. However, the result is that Hibernate does an N+1 to fetch all the UserNeeds.

What does this happen? The UserNeed also has some associations with other entities, which I have not included in the join fetch, do I also need to join fetch them to do it all in a single query?

Answer 1

You may want to specify at least one of your set as lazy loaded.

Answer 2

Adding the other associated collection as 'join fetches' in the query fixed it, and the whole thing ran in 1 query. I think using a query overrides the default fetch strategy, but the fact that there were other relations in there not being join fetched meant that Hibernate treated them differently.

I would be interested still to hear comments on whether adjuting the fetch strategy might also help, that is, should I have set the fetching strategy to 'join'? Anyway the N+1 is now fixed one way or another.