Lazy vs. Eager Loading in Hibernate

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.

To optimize this behavior I first tried to set the lazy=false attribute in the hibernate mapping file. I expected the behavior 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 interaction.

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.