Caching for SQLite

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

Replies are listed 'Best First'.
Re: Caching for SQLite by andreas1234567 (Vicar) on Sep 03, 2007 at 18:45 UTC
Wikipedia defines cache as: A collection of data duplicating original values stored elsewhere or computed earlier, where the original data is expensive to fetch. What operation have you determined to be too expensive to retrieve from your SQLite database? If SQLite isn't up for the task in question, consider your options. -- Andreas	[reply]
Re^2: Caching for SQLite by bash (Scribe) on Sep 05, 2007 at 06:55 UTC
Expensive - Disk I/O. I have web site with ~30k uniq host/day. All pages at this site are generated by perl+SQLite. Database size ~ 5Gb. Database updates only once per day, so it's should be very easy to cache popular sql's select statements.	[reply]
Re^3: Caching for SQLite by Corion (Patriarch) on Sep 05, 2007 at 07:24 UTC
You shouldn't cache the results of SQL select statements but better cache the complete results, for example as HTML. Or consider creating the complete website as static pages (or at least the parts that will be requested most). If you need authentication etc., have that part dynamic but include the static parts directly from disk.	[reply]
Re: Caching for SQLite by Anonymous Monk on Sep 03, 2007 at 16:18 UTC
define "solution"	[reply]
Re^2: Caching for SQLite by bash (Scribe) on Sep 05, 2007 at 06:58 UTC
something like this `sub get_foo_object { my $foo_id = int(shift); my $obj = $Cache->get("$foo_id"); return $obj if $obj; $obj = $db->selectrow_hashref("SELECT .... FROM foo f, bar b ". "WHERE ... AND f.fooid=$foo_id"); $Cache->set("$foo_id", $obj); return $obj; }` [download]	[reply] [d/l]