Building the Optimized Beast

All of what we've seen so far in this last of the mod_perl trilogy involves preparing an optimized environment. We've pre-loaded common Perl modules and prepared connections to the database as well as some specific SQL statements. Leveraging all this in the active Perl script is the key, and it's not too difficult to boot.

We'll build a very simple Perl script here, which will retrieve the user's ID as submitted from a browser, and spit out their name to the web page. The point isn't the outcome, but our implementation, whose principles you can adapt to far more complex arrangements.

#!/usr/bin/perl
use strict;

use CGI;
use DBI;


#prepare CGI object and browser for output

my $cgiobj=new CGI;
print $cgiobj->header;


#retrieve persistent database handle
my $dbh=MyPerl::FastDB->connect;

#retrieve form data
my $userID=$cgiobj->param("userID");

#setup statement handle and bind parameter
my $sth=$MyPerl::FastDB::selectUserFromID;
$sth->bind_param(1,$userID);

#execute SQL statement
$sth->execute || die "Statement failed: ".$dbh->errstr;

#retrieve result using column binding
my ($firstName,$lastName);
$sth->bind_columns(\$firstname,\$lastName);
$sth->fetchrow_arrayref;

print "Hello, $firstName $lastName";

Despite the fact that the CGI and DBI modules were pre-loaded into the Apache parent server, we must still use them in this Perl script as usual, even though they will not be compiled a second time. After we create an instance of the CGI object and output an HTML header, we retrieve a database handle from the persistent connection into a local scalar variable, $dbh, as well as the userID value submitted from a fictional web form.

Now the juicy bits: the local scalar variable $sth is assigned a statement handle for the pre-compiled SQL statement map that we built into fastdb.pl, designed to query the database for a user's name given their ID. To get this statement ready to go, we need to substitute a real value for the placeholder, using bind_param on the statement handle, we bind the value of $userID to the first placeholder in the statement map (although our map only had one placeholder, you could use multiple placeholders for certain statements).

The statement is executed, and the script dies with an error message report by the database handle should the statement fail for some reason. Assuming success, we simply need to retrieve the result from the database. Here, we use another optimization called column binding -- we setup two local variables which are prepared to receive the results from the database, $firstName and $lastName. The bind_columns call tells the statement handle that these two variables will be the destination for the results -- notice that these variables are passed as references, preceded by forward slashes.

The fetchrow_arrayref call, the fastest way to retrieve data from the database, will place the return values into the bound parameters, which we simply output to the web page.

There's nothing miraculous about the outcome of our script -- it's very basic. And you could have coded the same functionality without ever reading this article. The difference is that the above is fast -- much faster than had we coded the same script using an unoptimized mod_perl environment, and will hold up to repeated execution more elegantly, saving processor time and computing resources, allowing for more hits more frequently.

Database Savoir Faire
The Perl You Need to Know
Conclusion and Resources