PHP Application Development With ADODB (part 2) - Cache Cow (
Page 4 of 7 )
One of the coolest things about ADODB has to be its support for cached
queries. Why? Because caching your queries can result in a fairly significant
performance improvement, especially if you're executing the same tired old
SELECT every time.
In order to illustrate the difference, let's take a
look at how this normally works:
<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");
// include the ADODB library
include("adodb.inc.php");
// create an object instance
// configure it for a MySQL connection
$db = NewADOConnection("mysql");
// open connection to database
$db->Connect("localhost", "john", "doe", "db278") or die("Unable to
connect!");
// execute query
$query = "SELECT * FROM library";
$result = $db->Execute($query) or die("Error in query: $query. " .
$db->ErrorMsg());
// iterate through resultset
// print column data in format TITLE - AUTHOR
while (!$result->EOF)
{
echo $result->fields[1] . " - " . $result->fields[2] . "\n";
$result->MoveNext();
}
// get and print number of rows in resultset
echo "\n[" . $result->RecordCount() . " rows returned]\n";
// close database connection
$db->Close();
?>
This should be familiar to you by now - it's a very basic SQL
SELECT operation with ADODB. If this was your personal Web site, and you were
getting 5000 hits a minute, you'd be running the query above 30,000 times an
hour. As you might imagine, this will have your database server scurrying around
like a hamster on cocaine - not to mention affecting the performance of your Web
site.
ADODB offers a better option - caching the results of the first
SELECT query, and using this cached resultset in each subsequent run of the
query. This reduces the load on the database server, and can also provide you
with an incremental performance benefit.
Here's what the revised script
looks like:
<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");
// include the ADODB library
include("adodb.inc.php");
// set cache location
$ADODB_CACHE_DIR = '.';
// create an object instance
// configure it for a MySQL connection
$db = NewADOConnection("mysql");
// open connection to database
$db->Connect("localhost", "john", "doe", "db278") or die("Unable to
connect!");
// execute query
$query = "SELECT * FROM library";
$result = $db->CacheExecute(300,$query) or die("Error in query: $query.
" . $db->ErrorMsg());
// iterate through resultset
// print column data in format TITLE - AUTHOR
while (!$result->EOF)
{
echo $result->fields[1] . " - " . $result->fields[2] . "\n";
$result->MoveNext();
}
// get and print number of rows in resultset
echo "\n[" . $result->RecordCount() . " rows returned]\n";
// close database connection
$db->Close();
?>
The first argument to CacheExecute() is the number of seconds
to cache the query results; the second is, obviously, the query string itself.
The remainder of the script remains unchanged - a cached resultset is processed
in exactly the same manner as a non-cached one.
You can also use the
CacheFlush() method to flush all queries from the cache.
/ 8 
