JPA/Hibernate query optimization with null values

Question

I'm using Hibernate's implementation of JPA and am seeing poor performance as multiple SQL queries are issued for each entity that is fetched. If I use a joined JPA query it generates just one SQL query but doesn't find rows will null relationship.

Example, consider this simple schema. A person lives at an address and is employed by a company. Both address and employer are optional and can thus be null.

@Entity
public  class Person {
    public name;

    @ManyToOne
    @Column(nullable=true)
    public Address address

    @ManyToOne
    @Column(nullable=true)
    public Company employer
}

@Entity
public  class Address {
    address attributes ...
}

@Entity
public  class Company {
    company attributes ...
}

Not shown above is that each JPA entity has some sort of ID (key):

@Id
public Integer id;

The problem I'm seeing is that a single JPA query on Person results in multiple SQL queries on the database. For example, the following JPA query:

select p from Person p where ...

results in the SQL query:

select ... from Person where ...

and also the following pair of SQL queries for each retrieved person:

select ... from Address a where a.id=xxx
select ... from Company c where c.id=yyy

This has a huge impact on performance. If the query result set is 1000 people, then it generates 1+1000+1000=2001 SQL queries.

So I tried optimizing the JPA query by forcing it to join:

select p from Person p join p.address a join p.employer e where ...

or:

select p, a, e from Person p join p.address a join p.employer e where ...

This results in one single SQL query with a bunch of joins. The problem is if address or employer is null, then the joined query won't find it.

So I'm left with either using the join-less query which is slow, or the fast joined query that doesn't retrieve rows will null relationships. I must be missing something here. Surely there's a way for fast and complete querying.

Answer 1

My guess is that you'd need a left join, i.e.,

SELECT p FROM Person p LEFT JOIN p.address a LEFT JOIN p.employer e WHERE...

See this blog entry for an example

Note that I haven't actually tried this with JPA, but it works fine in HQL, which is the basis for the JPA standard in many ways.

The reason it's not working with plain join's is that the default is an inner join.

Answer 2

Try setting a batchsize (@BatchSize) on the Address and Company entities. It wont load them in a join (is that what you're after?), but it will load a bunch of them each time one is loaded. The batchsize says how many it should load when it discovers that it needs one.

If you have a batchsize of 1 (the default), and load 10 people. Then iterate over them, reading their address and company items, then hibernate will do one query for the 10 people, then each time it needs the address or company for one of those people then it will make a query for that person's address.

If you have set a batchsize of 7 on the Address entity then, when you read the first address, it will see that there are more than 7 Addresses that are currently proxied, and will go and get you 7 of the addresses.

If you have both Address and Company with a BatchSize of 7 and you're iterating through 10 people, then this will result in 5 queries, rather than the 21 yo would get at the moment. Still not the 1 that a join wold give you. However, the join would be slower in the cases where you just want the Person objects and are not going to be touching the Address/Company entities embedded in them (say you just want to get a list of the person ids, or count how many are male/female)

Have a look at: http://hibernate.org/hib_docs/v3/reference/en/html/performance.html