Proper way to cache queries

[Bennett Hiles]

Is there a proper way to be caching the result set for a query?  

I currently create a implementation of my DAO that checks the cache and if the value isn't found calls the jdbi DAO implementation and then stores it in a cache.  

The trouble I see with this implementation is:

•  the need to create a new implementation class for each DAO 
• I don't know how to get the query string to potentially use as the caching key (along with the parameters) 
  

-Bennett

[Brian McCallister]

Exact caching semantics are so variable that there is no one rule for
this. In terms of tools, if I wanted a read-through cache, I'd
probably use Guava's Cache stuff (formerly CLHM) with a loader that
calls the dao. It should be relatively straightforward to make that
transparent a wrapper/proxy if that is the api you want.

-Brian

>
>
> -Bennett
>
> --
>
>

[Render Wood]

2 years later, are there now any ready solutions for this? I am already using jdbi-folder library to handle the one-to-many relationships between data-model objects so I assume the logic should be added somewhere inside folder?

Additional issue with folder is that between single and multiple returned objects, you'd always need to return FoldingList<YourType> because it's the FolderList that handles the joined tables.

[Brian McCallister]

Memcached is good for a shared cache, Guava has a good in-memory LRU cache.

Personally, I would not make caching transparent in query result processing machinery.

-Brian

On Mon, Dec 21, 2015 at 6:03 AM, Render Wood <rende...@gmail.com> wrote:

2 years later, are there now any ready solutions for this? I am already using jdbi-folder library to handle the one-to-many relationships between data-model objects so I assume the logic should be added somewhere inside folder?

Additional issue with folder is that between single and multiple returned objects, you'd always need to return FoldingList<YourType> because it's the FolderList that handles the joined tables.

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tatu Saloranta]

For what it is worth, +1 for keeping caching a separate concern, and not trying to bake it fully in with the machinery. Semantics of what can be cached, when, keeping track of timing gets tricky and is difficult to generalize.

One thing that might be low effort, reasonable gain would to be figure out if there are a some obvious hook-in points, from which calls to custom handlers would make it easier to plug-in caching. But I'm not sure even that would be simpler than one writing a higher-level abstraction that composes jdbi and a caching system, instead of trying to make jdbi sort of drive the whole thing.

-+ Tatu +-

[Render Wood]

IMO optimal place for it would be in JDBI DAO interfaces, maybe using some additional annotations about which queries should be cached, what is the cache-key and TTL and so on.

Doing higher level abstraction sounds complicated already because of JDBI proxy-generation, and handling those handles OR could you maybe proxy DAO handles somehow afterwards:

MyDAO dao = CacheManager.openCached(jdbi, MyDAO.class)?

Then again the configuration about what and how is cached needs to be somewhere: in your code where you use it, in another set of CachedDAO-interfaces or within the JDBI DAO interfaces as annotations.

[Matthew Hall]

If you have a practical idea for what caching would look like in terms of annotations, I invite you to try writing, perhaps using Guava caches for the first attempt, to see if you can get something working.

I must warn you however, that I and at least one other team member are rather opposed to putting caching into JDBI proper. In my opinion that is where other ORMs go off the rails--caching is just too big a surface area to do it justice and still be flexible enough to adapt to all deployment environments. Consider the following:

* What is the life cycle of the cache? Global? Session? Request? Lifetime of DAO?
* What if we want to use a distributed cache?
* What key do we cache on? The list of all arguments? One argument in particular? A subset?
* How to expire cached elements? TTL? Time since last read?
* How do we limit the cache so it doesn't use too much memory? How do we decide which elements to discard when we're getting close to the limit?
* How can we make it so calling one method invalidates an item in the cache? e.g. we might want the cache behind MyDao.selectFooById(long) to be invalidated by a call to MyDao.update(Foo). How do we express that in annotations? How can we invalidate only the item for Foo.getId()?
* What if what happens in one DAO needs to invalidate items cached by a different DAO?
* How do we protect the cached elements from external modification by callers after they receive the data from the DAO call? Serialization / deserialization? Cloning?

This is just the beginning of the Gordian knot that is caching in ORMs. It is very difficult to come up with a design that will please everyone.

My approach to caching has been to plug it in a level above the DAO. e.g. hide MyDAO behind MyService with the same methods, and take care of caching by hand in the service class.

If you can come up with a clean design that addresses my concerns I would be happy to entertain a PR, so long as the proposed solution is opt-in.

I recommend working off the jdbi3 branch as that is where the action is at these days.

Good luck. Feel free to pose questions on the mailing list if you run into problems.

-Matt

[Steven Schlansker]

I think the most credible approach will be effectively a separate caching
library built on top of jdbi, and if we need to add a couple of tasteful
hooks into the core library we'd seriously consider that.

If you work on jdbi3 you could position it as an add-on module in the
same tree.

I think the aversion to merging in a caching layer stems more from
the (perceived?) difficulty of writing a library that is correct
and performant across a wide range of use cases. In almost every
nontrivial cache I've used, you end up having to write custom
invalidation logic, which ends up being the hard part anyway.

But if you're excited to give it a shot, and can keep it decoupled
from core jdbi, I think we'd be excited to see where it goes!

[Brian McCallister]

s/(perceived?) difficulty/effective impossibility/

:-)

I concur with Steven's comments for the most part, though probably a bit more strongly.

Caching logic is too application dependent to provide in a transparent way, and is mostly orthogonal to a database access library. Some applications can do nicely transparent caching where the magic falls out nicely, but these are fairly rare. Baking this approach into JDBI would steer people towards using it when they shouldn't, creating lots of frustrating bugs.

In the cases where it would be appropriate, it can be added by the application. You *could* make a library to provide highly transparent caching/memoization for dao instances, but I don't recommend it. Transparent things like that usually lead to subtle bugs of the worst kind (data corruption), and folks blame the library (rightly to some degree, it encouraged bad behavior).

-Brian