Efficient Hibernate criteria for join returning many partial duplicates

Question

I'm fetching a long list of entities which refer to others which refer to... and, at the end, usually of all them refer to a single user as their owner. Not really surprising as what's queried are entities belonging to a single user. There are more parts duplicated in many rows; actually, just a small percentage are unique data. As the query seems to be slow, I though I could gain a bit by fetching things separately using

criteria.setFetchMode(path, FetchMode.SELECT);

This works in my above case, but when querying over many users (as admin), it gets terrible, as hibernate issues a separate query for every user, instead of something like

SELECT * FROM User WHERE id IN (?, ?, ..., ?)

or not fetching them at all (which can't get any worse than one query per entity). I wonder what am I missing?

So instead of fetching a lot of redundant data, I ran into the 1+N problem, where obviously 1+1 queries would do.

• Is there a way to instruct Hibernate to use the right query?
• Is there a way to prevent Hibernate from fetching the owners by specifying it in the criteria itself (rather than putting fetch=FetchType.LAZY on the field; the laziness should be query-specific)?

---

I don't think it matters, but my classes are like

class Child {
    @ManyToOne Father father;
    @ManyToOne Mother mother;
    ...
}
class Father {
    @ManyToOne User owner;
    ...
}
class Mother {
    @ManyToOne User owner;
    ...
}

and the query is like

createCriteria(Child.class)
.add(Restrictions.in("id", idList))
.add(Restrictions.eq("isDeleted", false))

.createAlias("Father", "f")
.add(Restrictions.eq("f.isDeleted", false))
.setFetchMode("f.owner", FetchMode.SELECT)

.createAlias("Mother", "m")
.add(Restrictions.eq("m.isDeleted", false))
.setFetchMode("m.owner", FetchMode.SELECT)

.list();

The important part is that owner does not get used and can be proxied. The javadoc for FetchMode.SELECT says

Fetch eagerly, using a separate select

so it basically promises 1+1 querying which I want rather than "using a separate select per entity".

Answer 1

Fetch profiles are meant to help you achieve what you want, but are very limited at the time being and you can override the default fetch plan/strategy only with the join-style fetch profiles (you can make a lazy association eager, but not vice versa). However, you could use this trick to invert that behaviour: Make the association lazy by default and enable the profile by default for all sessions/transactions. Then disable the profile in transactions in which you want lazy loading.

IMHO the solution above looks too cumbersome, and the approach I use in most use cases to avoid both loading of redundant data and N+1 selects problem is to make associations lazy and define batch size.

Answer 2

I wrote a small project to demonstrate the behavior. The SQL generated from your criteria is as follows:

select
    this_.id as id1_0_4_,
    this_.father_id as father_i3_0_4_,
    this_.isDeleted as isDelete2_0_4_,
    this_.mother_id as mother_i4_0_4_,
    f1_.id as id1_1_0_,
    f1_.isDeleted as isDelete2_1_0_,
    f1_.owner_id as owner_id3_1_0_,
    user5_.id as id1_3_1_,
    user5_.isDeleted as isDelete2_3_1_,
    m2_.id as id1_2_2_,
    m2_.isDeleted as isDelete2_2_2_,
    m2_.owner_id as owner_id3_2_2_,
    user7_.id as id1_3_3_,
    user7_.isDeleted as isDelete2_3_3_ 
from
    Child this_ 
inner join
    Father f1_ 
        on this_.father_id=f1_.id 
left outer join
    User user5_ 
        on f1_.owner_id=user5_.id 
inner join
    Mother m2_ 
        on this_.mother_id=m2_.id 
left outer join
    User user7_ 
        on m2_.owner_id=user7_.id 
where
    this_.id in (
        ?, ?
    ) 
    and this_.isDeleted=? 
    and f1_.isDeleted=? 
    and m2_.isDeleted=?

1. Changing the FetchMode in the criteria API did not affect the query. The owner data is still queried.
2. The Ids are in the "in" clause and Hibernate did not issue separate queries for each Id.

As mentioned in other answers, if the entity relation is set to EAGER, ie JPA default, then you can't change the fetch mode in the Criteria API. The fetch mode needs to be changed to LAZY.

You can see it here

Answer 3

To summarize my frustration... Hibernate is full of surprises (bugs?) in this respect:

• unless the property is declared with @ManyToOne(fetch=FetchType.LAZY), you can't change anything
• the default is FetchType.EAGER, which is stupid, as it can't be overridden
• using criteria.setFetchMode(path, FetchMode.SELECT) is pointless as it's always a no-op (either it gets ignored because of the non-overridable eagerness of the property or the property is lazy already)!
• fetching lazily leads by default to the 1+N problem
• it can be controlled via a class-level @BatchSize annotation
• placing a @BatchSize annotation on a scalar field gets silently ignored

In order to get what I wanted (two SQL queries), I need just two things:

• declare the property with @ManyToOne(fetch=FetchType.LAZY)
• place @BatchSize(size=aLot) on the class of the property

That's simple, but a bit hard to find out (because of all the ignored things above). I haven't looked into fetch profiles yet.

Answer 4

unless the property is declared with @ManyToOne(fetch=FetchType.LAZY), you can't change anything

True, at least for the time being, until fetch profile capabilities are extended to provide the ability to change eager loading to lazy.

the default is FetchType.EAGER, which is stupid, as it can't be overridden

True, and I agree that it is bad, but in Hibernate native API everything is lazy by default; it is JPA that mandates to-one associations to be eager unless explicitly specified otherwise.

using criteria.setFetchMode(path, FetchMode.SELECT) is pointless as it's always a no-op (either it gets ignored because of the non-overridable eagerness of the property or the property is lazy already)!

With it you should be able to override other lazy fetch modes. See HHH-980 and this comment from one of the lead Hibernate contributors about the javadoc confusion.

fetching lazily leads by default to the 1+N problem

It has nothing to do with lazy loading specifically, it is the default for eager loading as well if you don't fetch the eagerly loaded association in the same query.

it can be controlled via a class-level @BatchSize annotation

You have to place it on class-level for it to take effect on to-one associations with that entity; this answer is helpful. For collection associations (to-many associations with that entity defined in other entities) you have the flexibility to define it separately for each association.