Can DBI statement handles use cached calls to execute()?

Question

I have an application where the database rarely changes, and the application requires many reads from the database that is slowing down the performance quite significantly. Many of these reads are exactly the same. So I want to get DBI to cache the results of a database read.

For example,

$sth = $dbh->prepare('SELECT a, b FROM a_table WHERE c = ?');
$sth->execute(5);
$sth->execute(2);
$sth->execute(5); # this call loads the cached result set

I first thought this is what prepare_cached does, but I realised that it only caches the statement handle itself and not actual executions of the statement handle.

I suppose I could achieve what I want by wrapping the statement execution inside a memoized sub. But I'm just seeing if there is a shortcut within DBI itself.

Answer 1

as you said, the prepare_cached is related to the statement handle, and you need to cache the results of the execution. Memoize is good, but probably you need invalidate the cache from time to time, and re-execute the query to get a fresh copy from database. I'd use the Cache (http://search.cpan.org/perldoc?Cache) module. I've just copied this fragment from the introduction:

use Cache::File;

my $cache = Cache::File->new( cache_root => '/tmp/cacheroot' );
my $customer = $cache->get( $name );

unless ($customer) {
   $customer = get_customer_from_db( $name );
   $cache->set( $name, $customer, '10 minutes' );
}

return $customer;

You can use in memory cache instead of File. This example uses the $customer value from cache if exists and it's valid, otherwise gets a fresh value and store at cache (with 10 minutes of life).

Hope this helps.