Hibernate Querying Optimization - HQL with joins

The problem:

For each object that Hibernate loads, it needs to do one or more extra SQL queries to load associated objects. (Potentially as many as Kn + 1, where n is the number of objects returned).

Example:

• 
  
  Simplified hibernate mapping file
  
  
  

  <class name="Department">
  

  
  

  …
  

  
  

  <many-to-one name="employee".../>
  

  
  

  …
  

  
  

  </class>
  

  
  


• 
  
  Simple HQL Query
  
  
  

  from Department dept where dept.location like :LOCATION
  

  
  


• The generated SQL statements:
  

1. 
   
   At least the initial select.
   
   
   

   select dept_id, deplt_name, dept_location...from department...
   

   
   


2. 
   
   Then a sequence of selects from the associated table. (n sql select statements)
   
   
   

   Select ...from employee...
   

   
   

   Select ...from employee...
   

   
   

   Select ...from employee...
   

   
   

   ...
   

   
   If Sale table returns 100 rows, you will see 100 select statements extra more than the main select statement.
   

The solution (1):

The association objects must be loaded in the initial query and just when you need them. This can be done by adding 'left join fetch' for example and switching the lazy loading on.

from Department dept left join fetch dept.employee

where dept.location like :LOCATION

This will generate just one sql select statement.

The solution (2): When there are a lot of association tables

You don't want to end up with query like this:

from Department dept

left join fetch dept.employee

left join fetch dept.manager

left join fetch dept.building

left join fetch dept.level

left join fetch dept.city

…etc

The approach involves determining exactly which columns you really need, and instantiating data-transfer objects containing exactly those columns.

Select new Department (dept.id, dept.employee.name, dept.manager.name, …) from Department dept

…

This technique is fast and efficient, and avoids the overheads of handling associated objects and of loading large numbers of persistent objects into the Hibernate session cache. The only downside is the need to create a dedicated data-transfer class for each query, and an overloaded constructor.

Reference: http://www.javalobby.org/articles/hibernate-query-101/