how to limit size of hash

albert has asked for the wisdom of the Perl Monks concerning the following question:

I'm doing a series of database queries, which caches the statement handles. Usually this works fine and makes life good, but sometimes, the queries vary as I loop over the following code, and eventually I have memory problems. Then, things go bad (aka crash!).

  if (my $sth = $self->{$dbh}{$query}) {                              
+                                                                
    warn "Using cached statement handler\n" if $self->debug;          
+                                                                
    return $sth;                                                      
+                                                                
  } else {                                                            
+                                                                
    warn "Creating new statement handler\n" if $self->debug;          
+                                                                
    $sth = $dbh->prepare($query) || $self->throw("Couldn't prepare que
+ry $query:\n ".DBI->errstr."\n");                               
    return $self->{$dbh}{$query} = $sth;                              
+                                                                
  }
[download]

I'd be interested in limiting the number of cached statement handlers, as the caching is only really worthwhile when I am not constantly making new ones. Does anyone have a suggestion on how I could go about this?

I was considering some sort of size limited tied hash, and then shifting an element off, but was looking at what others might suggest.

Comment on how to limit size of hash Download Code

Replies are listed 'Best First'.
Re: how to limit size of hash by Limbic~Region (Chancellor) on Oct 26, 2004 at 16:10 UTC
albert, You appear to be in a catch-22. For some reason, the normal solution ($dbh->prepare_cached) is not working and the alternatives will likely defeat the purpose. There are performance impacts using tied interfaces on top of managing your own cache. Other than the modules already suggested, Tie::Cache looks like a close match to what you want. It works on the least recently used concept allowing you to specify limits based on quantity and size. I also rolled my own because it looked like fun: Read more... (1015 Bytes) This naive approach divides the user defined max between two hashes. When the limit has been reached, the first hash is moved to the second and the first starts over. Cheers - L~R	[reply] [d/l]
Re: how to limit size of hash by gaal (Parson) on Oct 26, 2004 at 14:58 UTC
I was considering some sort of size limited tied hash, and then shifting an element off, but was looking at what others might suggest. Sounds like a good solution to me. CPAN has some modules already, but this is probably the kind of thing I'd do myself.	[reply]
Re: how to limit size of hash by revdiablo (Prior) on Oct 26, 2004 at 18:50 UTC
I was considering some sort of size limited tied hash Since some of the earlier posts seem to be concerned about the performance of a tied solution, you might consider a straightforward subroutine instead. You could use a closure to store both the cache, and an array holding the insertion order (for use in deleting the oldest value). Here's a quick example I worked up. This is not extremely well-tested, but does appear to work. YMMV: `# simple test for (0, 1, 2, 3, 4, 5, 6, 0, 0, 4) { print "$_ ", cache($_), "\n"; } { my @order; my %cache; sub cache { my ($key) = @_; # this number would need tweaking if (@order > 5) { delete $cache{ shift @order }; } unless (exists $cache{$key}) { push @order, $key; $cache{$key} = some_expensive_func($key); } return $cache{$key}; } } # here is where you would build your statement handles sub some_expensive_func { $_[0] + 5 * 3; }` [download]	[reply] [d/l]
Re: how to limit size of hash by hardburn (Abbot) on Oct 26, 2004 at 15:37 UTC
Why aren't you using `$dbh->prepare_cached`? "There is no shame in being self-taught, only in not trying to learn in the first place." -- Atrus, Myst: The Book of D'ni.	[reply] [d/l]
Re^2: how to limit size of hash by albert (Monk) on Oct 26, 2004 at 15:44 UTC
I'm having trouble with Postgres, and `$dbh->prepare_cached` has no effect with the DBD::Pg driver. -a	[reply]
Re: how to limit size of hash by davido (Cardinal) on Oct 26, 2004 at 15:19 UTC
If you're getting into using tied arrays or hashes, you could always tie one to a file or dbm, so that there is no in-memory footprint for the cache of statement handles. Of course this itself would create a performance penalty that might be more than the penalty of just re-preparing a statement handle, so you'd have to benchmark to be sure. Dave	[reply]