Hibernate query cache - for objects not in the 2nd level cache - risky? useful? bad practice?

Question

Related to this question

Premise:

These are my assumptions, based on my reading, experience and understanding, they may be wrong, if they are, please comment and I'll edit the question.

• Query cache is good mostly along with 2nd level cache
• Query cache caches the identifiers results of queries + parameters
• Query cache is risky if the database was changed, and it wasn't reflected to the cache

Question:

I have an object that is not in the 2nd level cache. Due to some bad programming or other constraints, the code that loads the object is being called several time in the same hibernate session. The retrival is using an HQL find query e.g.

 hibernateTemplate.find("from Foo f where f.bar > ?", bar);

Before adding query cache, if the above code was called N times within the same Hibernate Session, there were N hits to the database

Then I wanted to see what happens if I add query cache:

 Query query = session.createQuery("from Foo f where f.bar > ?");
 query.setCacheable(true);
 query.setParameter(bar);
 query.list();

When I added query cache, i've noticed that during the same session, hibernate doesn't hit the database N times anymore, only once per session.

1. So my first assumption is that Hibernate first searches in the Session Cache, then in the 2nd Level Cache. Is this assumption correct?
2. I also assume that if the object (Foo) which is not in the 2nd level cache, was changed in the database, then query cache, being cross session scoped, will return the wrong identifiers, and thus the wrong objects. Is that correct?
3. Is it then safe to say that using query cache for queries that include immutable information even for non 2L cached objects, is a good practice? (e.g. a query that its where clause contains a condition that will always return the same results, e.g. "select p.ser_num where p.id = ?" when ser_num and id couples do not change once created)

By the way, in the related question it is claimed that query cache doesn't work on the Session Cache scope. Am I missunderstanding that claim, or anything else?

Yseult

+1 for excellently formed, formatted and researched question. – Behn

Answer 1

A query cache is a particular type of 2nd level cache. What you refer to as 2nd level cache I'd prefer to call "object cache".

Comments on your assumptions:

• Query cache is good mostly along with 2nd level cache (aka object cache).

A query cache only holds the raw results of the queries as primary keys, in hibernate speak, id's. It does not hold the actual hydrated objects. This makes sense as when you execute a query with jdbc it only actually gives you back hydrated (populated) objects as you iterate over the ResultSet. The statement is not necessarily correct. If the query is very complicated and thus takes a very long time to run, by using a query cache you will save that time. You will not, by using a query cache, save the time it takes to load the objects from the database.

• Query cache is risky if the database was changed, and it wasn't reflected to the cache

This is true, but it is not unique to query cache, the same holds true for what you term 2nd level cache, but what is typically called object cache.

So my first assumption is that Hibernate first searches in the Session Cache, then in the 2nd Level Cache. Is this assumption correct?

Yes, when loading objects this is the behaviour.

I also assume that if the object (Foo) which is not in the 2nd level cache, was changed in the database, then query cache, being cross session scoped, will return the wrong identifiers, and thus the wrong objects. Is that correct?

Yes, both the object cache and the query cache will be affected. This is only of concern if the database is changed without going via hibernate. You can potentially mitigate the effect of this by setting the timeout of the query cache.

Is it then safe to say that using query cache for queries that include immutable information even for non 2L cached objects, is a good practice? (e.g. a query that its where clause contains a condition that will always return the same results, e.g. "select p.ser_num where p.id = ?" when ser_num and id couples do not change once created)

For these kind of objects there's no reason not to use both an object cache and a query cache.

And yes, query cache does not work at session level aka Level 1 cache. Thus the reason why when you execute the query again it hits the database again. It will not put the result (id set) of a query into the session cache.

Bi

Thanks for the very thorough reply, one thing I don't understand. when you wrote "It will not put the result (id set) of a query into the session cache." - when it "hits the database again" does it execute the query as is? or just loads the objects by id's in a "select in" query? When the session scope is over, the query cache still should hold the ids even though they are of an object not in the 2nd level cache, right? – Yseult

If the query is not a cached query it will hit the database every time (the full query), as you saw. If it is configured to cache, it will hit the database the first time and not again. What I'd suggest you do is turn on sql logging either by the hibernate parameter or setting org.hibernate.SQL to DEBUG so you can see exactly when it is hitting the database. – Bi

Answer 2

only assumptions:

According to this article from Hibernate documentation:

[query cache] creates two new cache regions: one holding cached query result sets (org.hibernate.cache.StandardQueryCache), the other holding timestamps of the most recent updates to queryable tables (org.hibernate.cache.UpdateTimestampsCache).

I assume this means that whenever the timestamp of the queryable table is newer than the result-set - it will cause hibernate to approach the DB in the the next call for that query, and that what keep the query cache safe in some aspect.

But 2 sentences later in the same paragraph of the documentation it says:

The query cache should always be used in conjunction with the second-level cache.

again, my assumption is that this is the only way hibernate can be aware of changes to the database that are done out of this specific user's session

Doall

Answer 3

For your question #3, I don't think you want to use the query cache when the objects aren't cached. You'll end up with all the primary ids but it will have to hit the database once per key to retrieve the objects which may be slower than running the query with no caching at all. As of 3.3 anyway, maybe in newer versions it grabs the missing objects using fewer queries, e.g. where id in (:id1,:id2,...).

Crocket