Cache Cow - PHP

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.

>>> More PHP Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus