n+1 rather 1+n

Hello,
I have been thinking to post this for quite some time. This one is about the famous “n+1” behavior/problem with the ORM tools. Initially when I had learnt about this one it took me a while to digest. I felt “1+n” is more easier to understand than “n+1” and it would be so for Beginner audience. That’s the reason behind this post.

In ORM world you often mark the relationships as “lazy” so that they are lazily loaded. First we will see what this means,

Consider the classic one-to-many Department(One) and Employee(Many) relationship.

From Department’s perspective this one is represented as the Collection of Employee objects within a Department as shown below.

/**
 * The Employee Entity.
 * Only OneToMany Bidirectional association is shown in the code.
 */
@Entity
public class Employee {
    @ManyToOne
private Department dept;
}

/**
 * Department Entity.
 */
@Entity
public class Department {
    @OneToMany(mappedBy="dept")
private Collection employees;
}

When we mark this relationship as lazy and query for the Departments, ORM shall fetch the Department objects satisfying the criteria. Due to the “lazy” attribute ORM will not initialize the Collection of Employees until they are requested.

For e.g Consider the following data set –

When we query the database to get all the departments, ORM will initialize the Department objects except the Employee Collection. This corresponds to 1 query namely

SELECT * FROM DEPARTMENT

Later whenever we access the Employee Collection contained within the Department object,ORM will again have to go the Database and fetch the employees for that department.

The query will be something like

SELECT * FROM EMPLOYEE WHERE DEPTID=?

This means for whatever the number of departments(Let’s say “n” department records),the ORM will need to fire above query those many times. i.e. n times. So to iterate all the Departments along with their Employees ORM will fire “1+n” database queries in total.

This can be overcome by using eager fetching which will result in only one JOIN query that would collectively fetch Departments along with their Employees.

Hope this helps someone confused about 1+n problem.

Cheers,
Amit