One thing I've been milling around in my head for awhile in the JBoss CMP world is the concept of a query cache and I'm wondering if the concept could be applied to the Hibernate world.

Here are the parameters for a simply implemented query cache:

- Hibernate has sole access to datasource
- Access is read-mostly

This is how it would work.

1. Whenever a query is made, the fully expanding String of the query is stored in a hashmap (as the key), along with the result set (the value).

2. Whenever an update happens, the update figures out exactly what tables/object types are affected. Any query that is cached that is using any of these tables/object types is invalidated/flushed from the query cache.

I'm wondering if this is feasible to implement or even makes sense in the HB world.

Bill

setting up email notify so I can be notified when somebody responds.

Its _possible_. But I suspect that the usecases where it would actually result in a real performance improvement are very narrow.

Its been my view that caching of query results is something better handled in the application. But I can be persuaded otherwise, I guess...

www.djindexes.com is a usecase. I implemented a custom query cache for them for ejb finders. It is a read mostly sight. DB CPU utilization went from 100% to 2%.

I implemented a similar strategy for a similar client Banta media that had read mostly data they did reports on.

One more thing, anytime you're doing reporting mostly, like a decision support system or something, you'll want to cache queries.

Bill

ok, but in ejb, it is not really possible to do this caching in the application layer. In Hibernate it is. ('Cos of full detached object support.)

What you have to convince me is that there are big advantages doing it in Hibernate, rather than in the application.

Bill wanted to invalidate query if any of the object types related to it were updated.
It's not easy for an application to know which type is involved in a query when the select clause is restrictive (return cat out of a cat and dog from clause). Parsing and inheritance work is already done by hibernate.

That's a convincing argument: Hibernate would know when to invalidate a cached query; it's hard for an application to implement this (maybe possible via an Interceptor, but not really feasible).

So I'd like to revive the discussion: IMO, it would make indeed sense to introduce query caching to Hibernate.

Juergen

Guys, this functionality is already in 2.1 beta 4!

Oh :-) Great! I should have done my homework, I guess ;-)

Juergen

Oh :-) Great! I should have done my homework, I guess ;-)

Juergen