Some findings on Fetching Strategies and Performance

Recently we had to write some relatively more complex queries in some of our reports using Hibernate DAOs. We ran into some interesting findings that we would like to share…

one-to-one (in fact one-to-zero-or-one) association with lazy initialization.

For example we have a one-to-one association say from A to B. And suppose property b is null allowed. It appears that lazy loading via proxy might not be possible simply because a Proxy object is by definition not null. Therefore even if we have in B’s mapping file:

<class name="B" lazy="true" ...>

the default behavior is Hibernate will still “eager” load the association (basically performing an outer-join to table B).

We found a couple of interesting articles off of the Hibernate Community postings that was quite insightful:

  • A Short Primer On Fetching Strategies (http://www.hibernate.org/315.html)
  • Some explanations on lazy loading (http://www.hibernate.org/162.html)

The Resolution

The way we got around that was we specify fetchMode to SELECT in our Criteria for the report. This causes Hibernate to turn the fetching strategy of property b in our query to sequential selects rather than an outer join.

Lesson Learned

We should be mindful of the effects of mapping attributes that configures fetch strategy. E.g. attributes such as lazy, fetch, constrained, batch-size, etc, if we are not already doing so. This could have a dramatic effects on performance.

Moving forward

Perhaps we could capture all the queries in the system (possibly at runtime from our client tests) and run them through Informix EXPLAIN to review their execution plan. There are some interesting metrics in the result of EXPLAIN such as Estimated Cost (an integer), Estimated # of Rows Returned which can give us a quick clue of how the query is performing. And perhaps this could also be automated as well…

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply