In our application we have the typical hierarchical object organization that is reflected in the data store.  This fits nicely into a relational database.  Being a modern shop (surprising given the industry – defense) we use Hibernate for the ORM.  Unfortunately most developers, while somewhat familiar with Hibernate and SQL, have very little experience using HQL.

A typical problem would be list all the level 3 objects in the system.  So, you might find code like:

Set<Level3Id,Level3> hash = new HashSet<Level3Id,Level3>(); 
Set<TopLevel> topLevels = dao.findAll();
for(TopLevel top:topLevels) {
    Set<Level2> level2s = top.getLevel2s();
    for(Level2 level2:level2s) {
        Set<Level3> level3s = level2.getLevel3s();
        for(Level3 level3:level3s) {
            hash.put(level3.id, level3);
        }
    }
}

where the developer is constructing a HashSet to produce a unique list of level 3 objects.  This is a simple example, there are many use cases where we traverse much lower into the object graph using some complicated tests (WHERE clauses).

The alternative HQL is trivial in comparison and the performance improvement is astounding (remember due to lazy loading each of the objects above would have to be materialized even though we never really wanted them in the first place):


List<Level3> level3s = session
        .createQuery("select distinct level3 from Level3 as level3")
        .list();

I guess the message is to try and think about the underlying SQL database that you have available.  While Hibernate obscures access to the underlying database it normally exposes all of the functionality present in the SQL into HQL in some manner.  So, if you could do something neat in SQL chances are you can do the same neat access in HQL.