I recently gave a presentation at Jax 2008 where I talked about common problems on usage of database technologies and O/R mappers.

A general question is on whether to use lazy or eager loading. Generally lazy loading provides advantages as it does not pre fetch all referenced data from the database. However when details are required lazy loading can very easily result in the famous N+1 query problem. The image below shows the result of querying 100 persons and reading their addresses in a loop.

Hibernate Lazy Loading

To optimize this behaviour I first tried to set the lazy=false attribute in the hibernate mapping file. I expected the behaviour then to be to pre-fetch all addresses and as a result fewer SQL statements. However – as you can see below- the number of SQL statements is still the same. The only difference was that the SQLs are execute before iterating over the persons that during the interation.

Hibernate Eager Loading

This was not the desired result though. I wanted to have only one SQL being executed. To achieve this I had to modify the query using the join fetch keyword. Finally I had my optimized code executing only one SQL statement as you can see below.

Hibernate Eager Loading using Join Fetch

Related posts:

1. Understanding Caching in Hibernate – Part One : The Session Cache Hibernate offers caching functionality which is designed to reduces...
2. Understanding Caching in Hibernate – Part Two : The Query Cache In the last post I wrote on caching in Hibernate...
3. Understanding Caching in Hibernate – Part Three : The Second Level Cache In the last posts I already covered the session cache...
4. JPA Under The Hood – Understanding the Dynamics of Your JPA Framework I recently gave a talks on the behaviour of different...