In the DBI documentation, this is the recommended code for executing a query many times:
$sth = $dbh->prepare_cached($statement);
$sth->execute(@bind);
$data = $sth->fetchall_arrayref(@attrs);
$sth->finish;
However, I see that many* query methods allow passing a prepared and cached statement handle in place of a query string, which makes this possible:
$sth = $dbh->prepare_cached($statement);
$data = $dbh->selectall_arrayref($sth, \%attrs, @bind);
Is there anything wrong with this approach? I haven't seen it used in the wild.
FWIW, I have benchmarked these two implementations. And the second approach appears marginally (4%) faster, when querying for two consecutive rows using fetchall_arrayref
in the first implementation vs selectall_arrayref
in the second.
* The full list of query methods which support this are:
- selectrow_arrayref - normal method with prepared statements is fetchrow_arrayref
- selectrow_hashref - " " fetchrow_hashref
- selectall_arrayref - " " fetchall_arrayref
- selectall_hashref - " " fetchall_hashref
- selectcol_arrayref (doesn't really count, as it has no parallel method using the first code path as described above - so the only way to use prepared statements with this method is to use the second code path above)
I don't think there's really any advantage to using one over the other, other than that the first uses three lines and the second uses one (less possibility for bugs with the second method). The first might be more commonly used because the documentation states that the "typical method call sequence for a SELECT statement is prepare, execute, fetch, fetch, ... execute, fetch, fetch, ..." and gives this example:
Now, I'm not suggesting that programmers actually read the documentation (heaven forbid!) but given its prominence near the top of the documentation in a section designed to show you how to use the module, I would suspect that the more-verbose method is slightly more preferred by the module's author. As to why, your guess is as good as mine.