I am trying to use Hibernate query cache in my application.
Hibernate version: 4.3.11
EHCache version: 2.10.5
I am able to get the query cache working and it is caching the results so the subsequent executions of the same read query (with same parameters) is fetching data from the cache.
Now I try to insert a record, using hibernate API, that matches with the query predicates.
Once the record is inserted, I execute the read query again and it goes to fetch data from database and then subsequent executions again use cache.
From this what I understand is: when the record (that matches the query predicates and parameters) is inserted into the database, the query cache with old data is invalidated so the next query execution goes to database. The question I have here is: when the insert of record that matches the query predicates and parameters is done, can the hibernate also update the query cache with the new results so the query cache now has latest results and the next read query execution doesn’t need to go back to database ?
No, it can not. If you want that, you need to use the database-specific query caching features. In fact, if you properly tune your database server, it might be that you don’t even need the Hibernate query cache.
If you want that, you need to use the database-specific query caching features
do you mean the query caching capabilities provided by database?
if you properly tune your database server, it might be that you don’t even need the Hibernate query cache
Even if this is done, the database server will still be hit to fetch the data right? I want the database not to be hit once I cache the query in hibernate query cache and rest all will be handled automatically i.e. the cache will be updated by hibernate when a new record (that would affect the results of cached query) is inserted into the database.
do you mean the query caching capabilities provided by database?
If you’re using a database like Oracle, then yes.
Even if this is done, the database server will still be hit to fetch the data right? I want the database not to be hit once I cache the query in hibernate query cache and rest all will be handled automatically i.e. the cache will be updated by hibernate when a new record (that would affect the results of cached query) is inserted into the database.
If you tune the DB to store the working set in memory (e.g. buffer pool), the query can be served so fast that it makes no sense to use a query cache anyway. Assuming the query always takes less than 1 millisecond, why would you cache it?
If you tune the DB to store the working set in memory (e.g. buffer pool), the query can be served so fast that it makes no sense to use a query cache anyway. Assuming the query always takes less than 1 millisecond, why would you cache it?
Well… the product supports multiple databases viz. postgresql, oracle, sql server, mysql etc. so need to explore if each of the database supports that and how… which is going to be a challenge since we need to ask the customer to configure the database to use those caching capabilities.
Secondly, the application has multiple java processes which could be deployed on multiple nodes, however a single database instance is supported. Since multiple processes converge into a single db, the database could become a bottleneck even if it serves the results pretty quick.
If you use multiple databases, it’s mandatory to know how to tune them. And that applies to your customers too. Things get really bad without DB knowledge.
The DB does not have to become a bottleneck if you use database replication. Just spawn more nodes if you need more capacity for queries.
Thanks @vlad. Appreciate it. Will look at the Spring framework documentation for AbstractRoutingDataSource.
However, i would also like to look at the other 2 options: would you know any link to documents etc. that would provide details on these two approaches for balance requests between db nodes?
* at the JDBC Driver level * at the DataSource level