JPA Hibernate n+1 issue (Lazy & Eager Diff)

Question

I am trying to understand n+1 problem and thus find the proper fix.

I have two Entities: Company

@Entity
@Table(name="company")
public class Company implements Serializable {

    private static final long serialVersionUID = 1L;


    @Id
    @GeneratedValue
    private int id;

    @Column(name="cmp_id")
    private int cmpId;

    @Column(name="company_name")
    private String companyName;

    @OneToMany(fetch=FetchType.LAZY)
    @JoinColumn(name="cmp_id",referencedColumnName="cmp_id")
    private Set<Employee> employee;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getCmpId() {
        return cmpId;
    }

    public void setCmpId(int cmpId) {
        this.cmpId = cmpId;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public Set<Employee> getEmployee() {
        return employee;
    }

    public void setEmployee(Set<Employee> employee) {
        this.employee = employee;
    }



}

Employee

@Entity
@Table(name="employee")
public class Employee implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private int id;

    @Column(name="emp_id")
    private int empId;

    @Column(name="emp_name")
    private String empName;

    /*@ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name="cmp_id", referencedColumnName="cmp_id")
    @JsonIgnore
    private Company company;*/

    @Column(name="cmp_id")
    private int cmpId;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getEmpId() {
        return empId;
    }

    public void setEmpId(int empId) {
        this.empId = empId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

}

Each company have many employee. So simple UNI-DIRECTIONAL One to Many Relationship. Now when i am running query ("Select a from Company a"), i am facing n+1 selects(when i am trying to get employee)

But to understand the concepts more clearly, when i am changing it to EAGER, all the same n+1 queries are running initially(even when I did not fetch the employee). Is it the right behaviour? I mean shouldn't it fire a join query instead. Also how do I change code with EAGER to result in only 1 query.?

Answer 1

The "issue" is not really an issue, it's about how ORMs work. Hibernate creates so called nested queries if you access an association like that.

You are right, in both cases the same queries are executed, switching the FetchType of your mapping from LAZY to EAGER is only scheduling the execution of the additional (n+1) queries.

Assume you have many companies, all of them have employees, in both cases queries like that are executed (at least once):

select ... from company company0_

select ... from employee employee0_ where employee0_.cmp_id=?

The first one is executed to get all companies, the second one for every company once.

E.g.: 3 companies (N) with many employees will execute the first select once and three nested selects = 3+1 = 4 queries in sum.

The difference between the EAGER and LAZY is just about the point in time, you can't avoid database access in general because you nevertheless need the data. With LAZY the additional query is just postponed until you iterate the employee collection(s). But keep in mind that it's just a hint, not every database driver is supporting lazy loading.

If you really know that you always need the data you can write a FETCH JOIN query and receive all needed data in one shot:

Select c from Company c JOIN FETCH c.employee e

That will execute a query like:

select ... from company company0_ inner join employee employee1_ on company0_.cmp_id=employee1_.cmp_id

That will avoid the second database access. To verify that in your tests, maybe the datasource proxy project is something for you.

Answer 2

When facing with N+1 problem, there are several solution to overcome this issue. The native solution is to using @BatchSize annotation.

According to hibernate docs (http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#performance-fetching-batch):

20.1.5. Using batch fetching Using batch fetching, Hibernate can load several uninitialized proxies if one proxy is accessed. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can configure batch fetching: on the class level and the collection level.

Batch fetching for classes/entities is easier to understand. Consider the following example: at runtime you have 25 Cat instances loaded in a Session, and each Cat has a reference to its owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call getOwner() on each, Hibernate will, by default, execute 25 SELECT statements to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" batch-size="10">...</class>

With this batch-size specified, Hibernate will now execute queries on demand when need to access the uninitialized proxy, as above, but the difference is that instead of querying the exactly proxy entity that being accessed, it will query more Person's owner at once, so, when accessing other person's owner, it may already been initialized by this batch fetch with only a few ( much less than 25) queries will be executed.

So, you can add @BatchSize(size=10) to your relation (you can tune the size):

@OneToMany(fetch=FetchType.LAZY)
@JoinColumn(name="cmp_id",referencedColumnName="cmp_id")
@BatchSize(size = 10)
private Set<Employee> employee;

When you use @BatchSize annotation it adds a where query when fetching childs like :

select [columns] from child_table where parent_id in (?, ?, ? ...)

Another solution is to using join fetch in your queries:

entityManager.createQuery("select distinct c from Company join fetch c.emplee").getResultList();

When you use join fetch, it generates an inner join query. We add distinct keyword to avoid duplication because of inner join.

Answer 3

For more insight into what n+1 is check out this already well answered question.

To solve your problem you can fetch all the child entities eagerly by writing this query in your Company jpa repository.

@Query("Select c from Company c join fetch c.employee e where c.id = :cmpId")
public Company fetchCompanyAndEmployeesEager(@Param("cmpId") long id);

PS: I have not tested the query but it should work.