Chris Radcliff shows you how to improve the performance of database-backed Web sites using persistent Perl and Apache::DBI. Topics include opening persistent database connections, preparing cached Structured Query Language (SQL) statements, and using references to improve the efficiency of result sets. These techniques improve Web database responsiveness for any SQL database, including Oracle and MySQL, while reducing the overall load on the database server.

This article is excerpted from Perl for the Web, by Chris Radcliff.

When a Web site is using persistent Perl connections, improving database
access time isn't difficult. The persistent environment provides a new
layer of continuity between Web server requests, and this layer can be used to
maintain database connections and other cached information about the
database.

Performance blocks can be removed from Web applications at many levels:

The database connection architecture can be made more efficient using
Apache::DBI.

Database query preparing and execution can be streamlined using
placeholders and cached statements.

Perl's internal representation of the resultant data can be
reduced and accelerated by using references.

Using Apache::DBI

The Apache::DBI module has few features, but the most important is the simple
redefinition of DBI's connect method, which can improve Web
application performance immensely. Normally, a database connection is opened by
DBI each time the connect method is called, and the connection is
closed when the disconnect method is called, usually at the end of a
program or module.

In a Web Common Gateway Interface (CGI) context, this means that database
connections are opened and closed every time a CGI program is run, which could
be hundreds of times per second. Because a database connection takes up to ten
seconds to be established, this means that the database connection time is a
thousand times too slow to be acceptable.

In a persistent environment, Apache::DBI can be used to cache open
database connections based on the database accessed and the database user
specified when connecting. The connect method within DBI
defers to the same method within Apache::DBI if the environment is persistent,
which enables the Apache::DBI connect method to keep track of
open connections and to refresh them as needed.

Apache::DBI also overloads the disconnect method to
keep open database connections from being closed inadvertently by programs that
are written without Apache::DBI in mind. Because of this, no programs need be
modified and no tables need be restructured to use Apache::DBI. In fact,
it's better to invoke Apache::DBI outside the body of your program code to
ensure that it is used by all Web applications across the board. This can be
done by including a directive in the httpd.conf configuration
for Apache or the startup scripts for mod_perl, VelociGen, and other persistent
Perl environments, as shown in Listing 1.

Because of the unique relationship between DBI and Apache::DBI, the DBI
module checks when it is loaded to see if Apache::DBI is already in use. Thus,
if Apache::DBI is loaded before all occurrences of DBI in a Web application,
database connections are cached regardless of whether an individual script uses
Apache::DBI.

Additional performance gains can be achieved by creating a database
connection when the server starts each Perl engine. This is done by calling the
connect_on_init method in the startup script with the same
parameters that are used in the Web application, as in line 13 of Listing 1.
When doing this, make sure that the arguments passed to
connect_on_init are exactly the same as those used in the Web
application, including preference arguments such as AutoCommit
or RaiseError. Otherwise, Apache::DBI assumes that the
connections are different, and the initial connection are cached but never
used.

Note that the Apache server is not required to use Apache::DBI. Any Web
server that supports a persistent Perl environment can use Apache::DBI by
placing the use statement in the Perl engine's startup
script.