Hibernate Query Optimization and lazy loading

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:

  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/

Posted on February 8, 2010 at 12:00 pm by Sami · Permalink
In: Hibernate, IT · Tagged with: ,

Leave a Reply