How to handle SQL query result caching

Question

In a Perl program I cache SQL request result to speed up the program.

I see two common way to do that:

• Create a hash using the query as index to cache result, like suggested here
• Create a hash with but 2 index, first is the list of used table, second is where clause

I today used the 2nd option because it's easier to clean the cache for a given set of table when you know they have been changed.

My problem is to handle the cache cleaning, today most select query I do are against table with very few change. So when I run an update/delete/... I just clean up the hash table part that cache result for this table.

This has few impact on performance as I rarely have to clean the part of the hash that is often used.
But now for a program with more often update/delete on most table, this make my cache much less efficient as I often have to clean it.

How to deal with that ? My current cache system is quite simple, Cache::Memcached::Fast is quite complex. Do you have a solution that would be more efficient that mine but still quite simple ?

Answer 1

One approach I use for caching data that isn't likely to change (e.g. configuration data) is to use memoization, via the excellent Memoize module. I wrap the sql query in a function where I pass in the bind parameters and the table name, and memoize that function.

use Memoize;

sub get_config_for_foo
{
     my ($table, $field1, $field2) @_;

     # generate my sql query here, using table, field1 and field2...

     return $result;

}
memoize(get_config_for_foo);

You could also use a caching strategy in memcache or something similar; check out Tie::Cache::LRU for a good implementation for this.