Avoiding n + 1 selection using cached sleep associations or caching collections in general

Question

I have a one-to-many relationship: a parent record with n child records. These entries are often used and read only and are good candidates for caching.

Here is an approximate display of my hibernate:

`<class name="Parent" table="Parent> <cache usage="read-only"/> <id name="primary_key"/> <property name="natural_key"/> <set name="children" lazy="false" fetch="join"> <cache usage="read-only"/> <key-column name="parent_id"/> <one-to-many class="Child"/> </set> </class> <class name="Child" table="Child"> <cache usage="read-only"/> <id name="primary_key"/> <property name="parent_id"/> </class>` 

I often get the parent key using a natural key rather than a primary key, so I need to enable Query Caching in order to use the 2nd level cache (I use ehcache).

Here's the problem: when I get the parent element and get hit in the request cache, it becomes the query "fetch by primary key". This is perfect for the "one" end of my one-to-many relationship. If the parent element is not found in the cache, it is retrieved from the database. If my n child entries are not found in the cache, Hibernate retrieves them using n subsequent selective queries. The problem with choosing N + 1.

What I want is a way to cache a collection of child objects with the parent_id key. I want Hibernate to browse my collection in the cache as a whole, and not as a bunch of individual entries. If the collection is not found, I want Hibernate to retrieve the collection using the 1 select statement - fetch all children with parent_id = x.

Is this too much to ask Hibernate + ehcache?

Answer 1

I found my own answer - you can configure Hibernate + ehcache to do what I described above.

By declaring my child as a value type, not an entity type (I believe these are the terms that the Hibernate community uses), I can essentially refer to my child as a component of the parent, and not as a separate entity. Here is an example of my revised mapping:

 <class name="Parent" table="Parent"> <cache usage="read-only"/> <id name="primary_key"/> <property name="natural_key"/> <set name="children" lazy="false" fetch="join" table="Child"> <cache usage="read-only"/> <key-column name="parent_id"/> <composite-element class="Child"> <property name="property1" column="PROP1" type="string"> <property name="property2" column="PROP2" type="string"> </composite-element> </set> </class> 

The behavior of my child objects is slightly different from this configuration than it was before - there is no separate primary key defined for Child now, without shared references and without fields and columns with a zero value. See Hibernate docs for more on this.

My parent and child are read-only, and I really want to access the Child instances through the parent - I do not use Child regardless of the parent, so a value type treatment is suitable for my use.

The big win for me was how the collection is cached in my new configuration. The collection cache now caches my collection as a whole, with the parent_id key. For some, but not all, of my collection, it's no longer possible to be in the cache. The collection is cached and evicted as a whole. More importantly, if Hibernate searches my collection in a 2-tier cache and receives a missed error, it retrieves the entire collection from the database with a single request.

Here is my ehcache configuration:

  <ehcache> <cache name="query.Parent" maxElementsInMemory="10" eternal="false" overflowToDisk="false" timeToIdleSeconds="0" timeToLiveSeconds="43200" </cache> <cache name="Parent" maxElementsInMemory="10" eternal="false" overflowToDisk="false" timeToIdleSeconds="0" timeToLiveSeconds="43200" </cache> <cache name="Parent.children" maxElementsInMemory="10" eternal="false" overflowToDisk="false" timeToIdleSeconds="0" timeToLiveSeconds="43200" </cache> <ehcache> 

Hope this example helps someone else.