Hibernate Second-Level Query Cache not working Eager Fetching

Question

In NHibernate Profiler I observed that when I use eager fetching on an association, using "left join fetch" in an HQL Query or .SetFetchMode() in a Criteria Query the query no longer gets cached in the query cache.

In fact from what I can see only very basic queries are cached. If anyone can give me some insight into what queries get cached and which ones don't I will mark answer.

If it makes any difference, I'm using Memcached.... Is there a better choice for L2 Cache for a query-dense system?

I'm finding this rather challenging - if I don't use eager load I have the N+1 problem (but uses cache), if I do eager load, I get all the entities from the database, but with no caching.

It seems like there is quite a thick dividing line, both strategies have performance improvements but both strategies rob performance from the other strategy.

If anyone can give any insight into where abouts on this 'thick line' I should be to have optimal performance, or how to 'make the line thinner'... I would be very gateful and mark the answer.

Answer 1

Update: Please see my related question here. the bottom line is, try using fetch="select" to avoid joining with objects that are already in the 2nd level cache.

---

My previous answer (may still be usefull)

Query cache caches the identifiers returned from your query, not the actual objects

To use it properly you should

1. Use place holders (? or :varName)
2. Set query cache to true (you did)
3. The query should return objects, not properties (from Foo, not select foo.bar from Foo foo)
4. The returned objects should be either in the 2nd level cache, or subsequent calls are in the same hibernate session (same transaction)

To clarify #4, if 2 different transactions run the exact (cached) query with the exact parameters and returns the exact same object, but it is not in the 2nd level cache, a database hit will still occur to get the actual objects (probably a select .. in )

Query cache is useful for 2 things - avoid re hitting the database in the same transaction for HQL queries for non cached items, and allow utilizing 2nd level cached objects for HQL queries (automatically used in load or get commands)

Hope it cleared the forest...

Answer 2

I don't know about NHibernate, but in Hibernate, you have to explicitly enable query caching for a query use hints. L2 cache may cache individual objects automatically, but for queries it requires explicit directions.