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 us…

Accepted Answer

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`.

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

Solution

Problem

Related problems