First of all, I want to clarify that I'm talking about short lived Session, i.e. one Session per HTTP request in a servlet container.

In this scenario I think that it would make sense get a local Query Cache per session by default whit the possibility of disable it explicitly for specific queries (like we do for enable Query Cache with Criteria#setCacheable).

Why?

Well, the improvement of performance is obvious in cases where you execute the same query in different components of your application, e.g first in an advice to do some validations and after in a service that performs the business logic.

Besides that why we should execute the same query again if the Session Cache will end up asking to the StatefulPersistentContext if the objects are already hidrated and discarding the rows if they are?

There are a couple of situations when may be reasonably execute the query again:

1. You are doing a projection and you want it includes changes commited by other transactions.
2. You are doing a projection and you want it includes changes commited by the current transaction.
3. You are querying a list of entities and you want it includes entities created by other transactions.
4. You are querying a list of entities and you want it includes entities created by the current transaction.

Cases 2 & 4 could be done keeping track of what spaces have changed within the current transaction. This logic is already present in the UpdateTimestampsCache only that in this case it shouldn't be shared between transactions or replicated across servers. It's also present if we use FlushMode.AUTO.
Cases 1 & 3 should be explicitly treated via something like Criteria#skipLocalQueryCache.

It's true that we could achieve this local query cache using query.setCacheable(true).setCacheRegion(<localQueryCacheRegionID>); where localQueryCacheRegionID should be generated per request.

However this region would be invalidated by modifications of another sessions.

I'm not the first to think in a query cache on a session level only.

Thanks.

Any Cache is useful if it can be shared by multiple transactions. Real-life scenarios where the Query Cache is really necessary are rather rare because the RDBMS already ofefrs this functionality.

Every database has shared buffers, or buffer pools, which if you set correctly, you already have a strongly consistent Query Cache. After all, why do you think StackOverflow manages to provide an average response time of 25 ms for billions of queries with just 2 SQL Server nodes?

Why are you saying just 2 SQL Server nodes? I think you missed the part when Craver talks about Redis and ElasticSearch.



Aren't you underestimating the fact that an access to memory is far cheaper than an IO operation?



Thanks for the link to the series of stackoverflow, very interesting.

But that's exactly what I was telling you all along.



Shared buffers are memory mapped pages. Check out this article for more info.

So, the diagram about caching is actually supporting my argument. Just use the shared buffers properly, and only if you still need better performance, you should consider an application-level cache.

Vlad, correct me if I'm wrong but when you talk about database's shared buffers, aka memory mapped pages (we agree that they provide fast access) we are talking about database server, this means that we already incurr in a IO operation (network call) to hit the database from the application server (e.g. jboss)

An application-level cache will provide 100% memory access.

True. But then you have to take into consideration consistency as well. A database cache already provides it to you transparently. With an application cache, you'll have to ensure it yourself, and it's not as easy as you might think. Not to mention that DB might be changed by other systems as well.

Also, app-level caches operate on graphs of objects, and if you happen to modify a top-level entity, the change might ripple throughout many child entities. For more info, check my High-Performance Hibernate presentation from JavaZone.

Hi, we created a solution that caches SQL query result sets locally on the application server side. It auto-caches and auto-validates without any code changes. Hibernate supports both manual application and SQL caching. We just automated SQL caching.

In your experience, how problematic is the latency between application and database? If we can cache SQL locally, would it provide compelling benefits? Your feedback is appreciated.

Could you explain a little more about how do you achieve this?



About this, I don't actually get any metrics. I'm just basing my question in the knowledge that an access to memory is cheaper than a IO operation to the database, triggered by the fact that I'm reviewing a case when one ~200 entities of the same type are being retrieved one by one (neither by id nor by naturalId) in a validation advice and after that are being retrieved again in the same way in a business service.

Yes, a first improvement could be retrieve all the entities in a single query in both the advice and the service, paying only the second IO operation (that could trigger multiple round trips depending on the size of the retrieved data and the bandwith).

Besides that, my question is more conceptual because if Hibernate will end up throwing away all(*) the results because they are already hydrated, why execute the query again?
(*) There will be cases where the query could retrieve different results but the user should know in what cases he wants to get the updated results or always see the same result set. So the approach could be to explicit enable the session query cache for certain queries when the user not be interested in have 2 different result set for the same query (similar to what happens with an Entity when the user will always work with the same version, that being the first retrieved)



I don't see this, IMHO if you are executing the same query twice within a session is always in the hands of the developer to ensure consistency because you could potentially be working with two different result sets because, as you say, DB might be changed by other systems as well.