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.

  1. set lazy=”false” and use fetch=”join” so that the departments and its employees get loaded together. (Eager fetch)
  2. 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")