In my last two posts, I mentioned that immediate fetching or lazy fetching can cause ‘N+1 select problem’. If you are wondering what exactly is this, read on.
Consider the example of Department and Employees. When you call EntityLoad(“Department”), following sqls will be executed.
SELECT * FROM department; SELECT * FROM employees WHERE deptId = ?
The first query will be executed once (1) and the second query will be executed as many times as the department (N). Thus the above entityLoad call results into ‘N+1′ sql execution and thus can be a cause of performance bottleneck. Because of N+1 sqls, this is known as ‘N+1 select problem’. This will almost always happen when the fetching is “Immediate” (using fetch=”select”) or can happen with lazy loading.
With immediate fetching it is obvious why this would happen. When lazy=’true”, this can happen when the association is accessed immediately on each of the owning object (department in this case).
If you think this could be happening in your application, use either of these two options.
- set lazy=”false” and use fetch=”join” so that the departments and its employees get loaded together. (Eager fetch)
- Keep lazy=”true” but load the department using hql with join. So instead of using EntityLoad(“Department”), use
ORMExecuteQuery("from Department dept left join fetch dept.employees")
#1 by Hemant Khandelwal on September 14th, 2009
| Quote
Great posting (again) Rupesh!
The classic N+1 problem in encountered in a simple lazy load scenario in any general application.
1query to get all department objects with PK
SELECT deptID FROM department;
And then query to get all other details of a particular dept
SELECT * FROM department where deptID=?;
The second query is executed as many times (N) as the number of full records you want to fetch. Its when N is high, eager can be a good strategy.
#2 by Rupesh Kumar on September 15th, 2009
| Quote
Thanks Hemant for making it more clear
#3 by Ben Nadel on September 16th, 2009
| Quote
Rupesh, good explanation. It looks like we are really going to have to know a bit about how Hibernate works to really leverage this integration efficiently. – we can’t just start using it blindly.
#4 by Rupesh Kumar on September 16th, 2009
| Quote
Absolutely !
#5 by Henry Ho on March 25th, 2010
| Quote
What if I use HQL:
ormExecuteQuery(“from Employee e where e.department = ?”, [employee])
Is this a N+1 select problem?
#6 by Henry Ho on March 25th, 2010
| Quote
Oops, it should be [department] as the 2nd argument in the code above.
#7 by Rupesh Kumar on March 26th, 2010
| Quote
That will be loading the employee object only. So N+1 select problem will not come into play.
It comes into play only if you are loading an object which has *-many relation and the relation has fetch=”select” or it is lazy as I explained above.
#8 by Goods on January 19th, 2011
| Quote
Hi Rups,
Im new to Hibernate
Could u send me a simple concrete example of the above n+1 select concept and a solution -
for eg.
for the below class , could u send me the Database tables and the hbm.xml files to demonstrate
n+1/ lazy load ????
Thanks indeed !!!!!
class Dept {
long id;
Set employee;
public void setId( long l ) {
id=l;
}
public Long getId() {
return id;
}
public void setEmployee( Set emp ) {
employee = emp;
}
public void getEmployee() {
return employee;
}
}