When working with a relational database, you'll often encounter the need to read the retrieved set of records into your program, then format and print them to the browser.

Assuming that you're already connected to the database, let's consider the following code prototype:

my $query = "SELECT id,fname,lname FROM test WHERE id < 10";
my $sth = $dbh->prepare($query);
$sth->execute;

my @results = ( );
while (my @row_ary  = $sth->fetchrow_array) {
    push @results, [ transform(@row_ary) ];
}
# print the output using the the data returned from the DB

In this example, the httpd process will grow by the size of the variables that have been allocated for the records that matched the query. Remember that to get the total amount of extra memory required by this technique, this growth should be multiplied by the number of child processes that your server runs—which is probably not a constant.

A better approach is not to accumulate the records, but rather to print them as they are fetched from the DB. You can use the methods $sth->bind_columns( ) and $sth->fetchrow_arrayref( ) (aliased to $sth->fetch( )) to fetch the data in the fastest possible way. Example 20-1 prints an HTML table with matched data. Now the only additional memory consumed is for an @cols array to hold temporary row values.

Example 20-1. bind_cols.pl

my $query = "SELECT id,fname,lname FROM test WHERE id < 10";
my @fields = qw(id fname lname);

# create a list of cols values
my @cols = ( );
@cols[0..$#fields] = ( );
$sth = $dbh->prepare($query);
$sth->execute;

# Bind perl variables to columns.
$sth->bind_columns(undef, \(@cols));
print "<table>";
print '<tr bgcolor="grey">', 
    map("<th>$_</th>", @fields), "</tr>";
while ($sth->fetch) {
    print "<tr>", 
        map("<td>$_</td>", @cols), "</tr>";
}
print "</table>";

Note that this approach doesn't tell you how many records have been matched. The workaround is to run an identical query before the code above, using SELECT count(*)... instead of SELECT * ... to get the number of matched records:

my $query = "SELECT count(*) FROM test WHERE id < 10";

This should be much faster, since you can remove any SORT BY and similar attributes.

You might think that the DBI method $sth->rows will tell you how many records will be returned, but unfortunately it will not. You can rely on a row count only after a do (for some specific operations, such as update and delete), after a non-select execute, or after fetching all the rows of a selectstatement.

For selectstatements, it is generally not possible to know how many rows will be returned except by fetching them all. Some DBD drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. Thus, use of the rows method with select statements is not recommended.