- Using Apache::DBI
- Preparing Statements with Placeholders
- Preparing Cached Statements
- Retrieving Data Sets as References
- Binding Result Columns
- About This Article
Retrieving Data Sets as References
Although savings in connection and query time improve the efficiency of all Web application database queries, some queries benefit additionally from increased efficiency in retrieving the result data. Large result sets with many rows can be returned more quickly by retrieving the values through references.
A common way to retrieve the results of a database query is by calling the fetchrow_array method on the statement handle, as illustrated in line 11 of Listing 2. This method returns one row's worth of data as a list of values that can be assigned to an array or individual variables, as in this example. Because DBI already stores the returned values internally, this requires those values to be copied to the new variables created for each row returned. For larger sets of query results, however, the overhead of creating variables and reassigning values can be expensive. In the case of Listing 2, the overhead of copying $title, $username, and $created would become noticeable with result sets as small as a few dozen rows.
DBI provides methods for retrieving results as references to the data structures provided by the driver, which removes the extra overhead caused by copying data to the application's own variables. The references are standard Perl structures that can be de-referenced within the application to use the associated values. Take care not to assign the de-referenced values to variables within the while block, however, because it invalidate the savings by performing the copy operation anyway.
Line 13 in Listing 4 uses the fetch synonym for the fetchrow_arrayref method, which is the fastest way to retrieve data from a statement handle. (Incidentally, fetchrow_hashref is provided for similar situations, but the performance improvements are not as great due to the overhead of creating the hash to reference.) Although Listing 4 doesn't assign the returned references to any values, it does use them inherently, as described in the next section, "Binding Result Columns." Because fetch incurs no overhead in terms of returned values or variable assignment, it serves perfectly as a row increment method for the while loop in this case.