- Using Apache::DBI
- Preparing Statements with Placeholders
- Preparing Cached Statements
- Retrieving Data Sets as References
- Binding Result Columns
- About This Article
Preparing Statements with Placeholders
When using DBI for database access, a query generally is passed to the database in two steps with a separate process used to return the result. The query is first sent to the database with a prepare method, which instructs the database to make an execution plan for the query and which returns a statement handle representing the prepared plan. Then, an execute method is called on the statement handle, which instructs the database to execute the plan and return the results to DBI. After DBI has the results, a variety of methods can be called on the statement handle to extract the data for use by the program.
One way to optimize database-to-Web performance is by using placeholders to stand in for data in an Structured Query Language (SQL) query. When a query is prepared by DBI, the database creates a plan of the best way to access the data needed to fulfill the query. The plan is simply thrown away after it is executed, leaving the database to create another plan from scratch. Unfortunately, a Web application is likely to make a similar query hundreds of times per second, so the database spends much of its time generating the same plan repeatedly.
Placeholders help this situation by giving the database a template of the type of query that will be used in a number of executed statements. The placeholders themselves—demarcated by a question mark—can indicate any value that would ordinarily be assigned to or compared against a data field. For instance, in this SQL query:
SELECT * FROM foof WHERE foo = 'bar'
the SQL subclause WHERE foo = 'bar' could be rewritten as WHERE foo = ?, with the value 'bar' being represented by a placeholder. It could not be written as WHERE ? = 'bar' because foo is a field name, not a value. The database accepts the placeholder in a template and makes a plan around it—thereby creating a more generic plan that then can be reused.
After a template has been sent through the DBI prepare method and an execution plan has been generated by the database, the DBI execute method can be called against the prepared plan that's stored in the DBI statement handle $sth (see Listing 2). This can be done as many times as needed without generating a new plan. The values specified by placeholders in the prepared template are passed as arguments to the execute method each time it is called. These values are analyzed by DBI to determine their data type if it hasn't previously been specified.
Listings 2 and 3 provide a comparison between a standard way of invoking DBI for a stand-alone (or CGI) Perl application and an accelerated example that assumes Apache::DBI is being used in a persistent environment.
Listing 2-Standard DBI Example
01 # show all the responses to this message
02 my ($childid, $title, $username, $created);
03 $sth = $dbh->prepare(q{SELECT m.msgid, m.title, u.username,
04 DATE_FORMAT(m.created, '%d %M %Y') as created
05 FROM messages m, users u
06 WHERE m.authorid = u.userid
07 AND m.parentid = $msgid
08 ORDER BY msgid DESC});
09 $sth->execute;
10
11 while (($childid, $title, $username, $created) = $sth->fetchrow_array)
12 {
13 print qq{<li><a href="tree.psp?msgid=$childid">$title</a>};
14 print qq{ by $username on $created</li>\n};
15 }
16 $sth->finish;
Listing 3-Accelerated DBI Example
01 # show all the responses to this message
02 my ($childid, $title, $username, $created);
03 $sth = $dbh->prepare_cached(q{SELECT m.msgid, m.title, u.username,
04 DATE_FORMAT(m.created, '%d %M %Y') as created
05 FROM messages m, users u
06 WHERE m.authorid = u.userid
07 AND m.parentid = ?
08 ORDER BY msgid DESC});
09 $sth->execute($msgid);
10 $sth->bind_columns(\($childid, $title, $username, $created));
11
12 while ($sth->fetch)
13 {
14 print qq{<li><a href="tree.psp?msgid=$childid">$title</a>};
15 print qq{ by $username on $created</li>\n};
16 }
17 $sth->finish;
In terms of placeholders, the differences between Listing 2 and Listing 3 are minor compared to the performance improvements achieved. The statement being prepared is almost identical with only one value in line 07 ($msgid) replaced by a placeholder. The rest of the statement is the same, no matter which arguments are passed to the program, so it can be left unchanged.
The only other placeholder-related change between the two listings is in the execute statement at line 09. The value that was originally used within the statement at line 07 now is being passed as an argument to the execute method, which fills the placeholder in the prepared execution plan with the value provided. DBI also determines the data type of the value provided by checking if the value is a number.
Placeholders can be made more convenient by binding variables to the input placeholders using the bind_param method. The example could be changed as shown in Listing 4 by adding a bind_param statement at line 07, which would enable the execute method in line 08 to be called without arguments, as it was originally. The placeholder is specified by number in the first argument, and then by the value to be used. The data type of the value can optionally be provided in case DBI would have a difficult time determining the type automatically. Additional values could be bound by adding a similar statement for each placeholder.
Listing 4-DBI Example with bind_param
01 # show all the responses to this message
02 my ($childid, $title, $username, $created);
03 $sth = $dbh->prepare_cached(q{SELECT m.msgid, m.title, u.username,
04 DATE_FORMAT(m.created, '%d %M %Y') as created
05 FROM messages m, users u
06 WHERE m.authorid = u.userid
07 AND m.parentid = ?
08 ORDER BY msgid DESC});
09 $sth->bind_param(1, $msgid, SQL_INTEGER);
10 $sth->execute;
11 $sth->bind_columns(\($childid, $title, $username, $created));
12
13 while ($sth->fetch)
14 {
15 print qq{<li><a href="tree.psp?msgid=$childid">$title</a>};
16 print qq{ by $username on $created</li>\n};
17 }
18 $sth->finish;
It is important to note that not all databases and DBI drivers support placeholders, and some drivers support placeholders differently than others. The examples in this article should work with most databases, but check the relevant driver documentation for specific examples of how to use placeholders with a particular database.