OneToOne relationship with shared primary key generates n+1 selects; any workaround?

Question

Imagine 2 tables in a relational database, e.g. Person and Billing. There is a (non-mandatory) OneToOne association defined between these entities, and they share the Person primary key (i.e. PERSON_ID is defined in both Person and Billing, and it is a foreign key in the latter).

When doing a select on Person via a named query such as:

from Person p where p.id = :id

Hibernate/JPA generates two select queries, one on the Person table and another on the Billing table.

The example above is very simple and would not cause any performance issues, given the query returns only one result. Now, imagine that Person has n OneToOne relationships (all non-mandatory) with other entities (all sharing the Person primary key).

Correct me if I'm wrong, but running a select query on Person, returning r rows, would result in (n+1)*r selects being generated by Hibernate, even if the associations are lazy.

Is there a workaround for this potential performance disaster (other than not using a shared primary key at all)? Thank you for all your ideas.

Answer 1

You could try "blind-guess optimization", which is good for "n+1 select problems". Annotate you field (or getter) like this:

@org.hibernate.annotations.BatchSize(size = 10)
java.util.Set<Billing> bills =  new HashSet<Billing>();

Answer 2

This is a common performance issue with Hibernate (just search for "Hibernate n+1"). There are three options to avoiding n+1 queries:

• Batch size
• Subselect
• Do a LEFT JOIN in your query

These are covered in the Hibernate FAQs here and here

Answer 3

Stay away from hibernate's OneToOne mapping

It is very broken and dangerous. You are one minor bug away from a database corruption problem.

http://opensource.atlassian.com/projects/hibernate/browse/HHH-2128

Answer 4

That "n+1" problem will only occur if you specify the relationship as as lazy or you explicitly indicate that you want hibernate to run a separate query.

Hibernate can fetch the relationship to Billing with an outer join on the select of Person, obviating the n+1 problem altogether. I think it is the fetch="XXX" indication in your hbm files.

Check out A Short Primer On Fetching Strategies

Answer 5

The "n+1" problem in OneToOne relationships is really annoying. I was trying to avoid HQL and use Criteria whenever possible, but if I use Criteria with lazy OneToOne relationships, it queries a lot of other tables I don't need.

Changing the OneToOne for a ManyToOne is too tricky, I'm trying to avoid this as well.

If I force the join with the OneToOne (eager load), the N+1 problem is replaced by a HUGE and DEEP join, which I didn't need after all.

Anybody came with any workaround?

Answer 6

Imagine 2 tables in a relational database, e.g. Person and Billing. There is a (non-mandatory) OneToOne association defined between these entities,

Lazy fetching is conceptually not possible for non-mandatory OneToOne by default, Hibernate has to hit the database to know if the association is null or not. More details from this old wiki page:

Some explanations on lazy loading (one-to-one)

[...]

Now consider our class B has one-to-one association to C

class B {
    private C cee;

    public C getCee() {
        return cee;
    }

    public void setCee(C cee) {
        this.cee = cee;
    }
}

class C {
    // Not important really
}

Right after loading B, you may call getCee() to obtain C. But look, getCee() is a method of YOUR class and Hibernate has no control over it. Hibernate does not know when someone is going to call getCee(). That means Hibernate must put an appropriate value into "cee" property at the moment it loads B from database. If proxy is enabled for C, Hibernate can put a C-proxy object which is not loaded yet, but will be loaded when someone uses it. This gives lazy loading for one-to-one.

But now imagine your B object may or may not have associated C (constrained="false"). What should getCee() return when specific B does not have C? Null. But remember, Hibernate must set correct value of "cee" at the moment it set B (because it does no know when someone will call getCee()). Proxy does not help here because proxy itself in already non-null object.

So the resume: if your B->C mapping is mandatory (constrained=true), Hibernate will use proxy for C resulting in lazy initialization. But if you allow B without C, Hibernate just HAS TO check presence of C at the moment it loads B. But a SELECT to check presence is just inefficient because the same SELECT may not just check presence, but load entire object. So lazy loading goes away.

So, not possible... by default.

Is there a workaround for this potential performance disaster (other than not using a shared primary key at all)? Thank you for all your ideas.

The problem is not the shared primary key, with or without shared primary key, you'll get it, the problem is the nullable OneToOne.

First option: use bytecode instrumentation (see references to the documentation below) and no-proxy fetching:

@OneToOne( fetch = FetchType.LAZY )
@org.hibernate.annotations.LazyToOne(org.hibernate.annotations.LazyToOneOption.NO_PROXY)

Second option: Use a fake ManyToOne(fetch=FetchType.LAZY). That's probably the most simple solution (and to my knowledge, the recommended one). But I didn't test this with a shared PK though.

Third option: Eager load the Billing using a join fetch.

Related question

• Making a OneToOne-relation lazy

References

• Hibernate Reference Guide
  • 19.1.3. Single-ended association proxies
  • 19.1.7. Using lazy property fetching
• Old Hibernate FAQ
  • How do I set up a 1-to-1 relationship as lazy?
• Hibernate Wiki
  • Some explanations on lazy loading (one-to-one)