What happened to this alternative mechanism for creating DBI queries?

Question

In the DBI documentation, this is the recommended code to execute 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 you to pass the processed and cached descriptor operator instead of the query string, which makes this possible:

$sth = $dbh->prepare_cached($statement);
$data = $dbh->selectall_arrayref($sth, \%attrs, @bind);

Is there something wrong with this approach? I have not seen it used in the wild.

FWIW, I checked these two implementations. And the second approach looks slightly (4%) faster when querying two consecutive lines using fetchall_arrayrefvs selectall_arrayrefin the second implementation in the first.

* A complete list of query methods that support this:

• selectrow_arrayref - normal method with prepared statements - fetchrow_arrayref
• selectrow_hashref - "fetchrow_hashref
• selectall_arrayref -" "fetchall_arrayref
• selectall_hashref -" " fetchall_hashref
• selectcol_arrayref ( , , , , )

Answer 1

, . select*_*, . DBI :

$sth = $dbh->prepare_cached($statement);
$sth->execute(@bind);
while (my $row = $sth->fetch) { # alias for fetchrow_arrayref
  # do something with @$row here
}

select*_*.

fetchall_* ( 1 ), select*_* .

Answer 2

, . - .

,

$sth->execute(@bind);
$data = $sth->fetchall_arrayref(@attrs);
$sth->finish;

,

sub selectall_arrayref {
    my ($dbh, $stmt, $attr, @bind) = @_;
    my $sth = (ref $stmt) ? $stmt : $dbh->prepare($stmt, $attr)
        or return;
    $sth->execute(@bind) || return;
    my $slice = $attr->{Slice}; # typically undef, else hash or array ref
    if (!$slice and $slice=$attr->{Columns}) {
        if (ref $slice eq 'ARRAY') { # map col idx to perl array idx
            $slice = [ @{$attr->{Columns}} ];   # take a copy
            for (@$slice) { $_-- }
        }
    }
    my $rows = $sth->fetchall_arrayref($slice, my $MaxRows = $attr->{MaxRows});
    $sth->finish if defined $MaxRows;
    return $rows;
}

, finish, ? , 5% ; .

: s//, /

Answer 3

selectall_arrayref() fetchall_arrayref(), fetchall_arrayref() fetch() . fetchall_arrayref() , C.

docs fetchall_arrayref ...

   If $max_rows is defined and greater than or equal to zero then it is
   used to limit the number of rows fetched before returning.
   fetchall_arrayref() can then be called again to fetch more rows.  This
   is especially useful when you need the better performance of
   fetchall_arrayref() but don't have enough memory to fetch and return
   all the rows in one go.

   Here an example (assumes RaiseError is enabled):

     my $rows = []; # cache for batches of rows
     while( my $row = ( shift(@$rows) || # get row from cache, or reload cache:
                        shift(@{$rows=$sth->fetchall_arrayref(undef,10_000)||[]}) )
     ) {
       ...
     }

   That might be the fastest way to fetch and process lots of rows using
   the DBI, but it depends on the relative cost of method calls vs memory
   allocation.

   A standard "while" loop with column binding is often faster because the
   cost of allocating memory for the batch of rows is greater than the
   saving by reducing method calls. It possible that the DBI may provide
   a way to reuse the memory of a previous batch in future, which would
   then shift the balance back towards fetchall_arrayref().

"".: -)

Answer 4

, - , , , ( ). , , " SELECT - , , , ,... execute, fetch, fetch,..." :

$sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");

$sth->execute( $baz );

while ( @row = $sth->fetchrow_array ) {
  print "@row\n";
}

Now I do not suggest that programmers really read the documentation (God forbid!), But, given its popularity at the top of the documentation, in the section designed to show you how to use the module, I would suspect that the more - the willow method is somewhat preferable by the author of the module. As for why your hunch is as good as mine.