Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Re^2: fastest method to use DBI

by dsheroh (Monsignor)
on Jul 07, 2009 at 14:28 UTC ( #777883=note: print w/replies, xml ) Need Help??

in reply to Re: fastest method to use DBI
in thread fastest method to use DBI

Yes, bind_columns/->fetchrow_arrayref (or its alias, ->fetch) is the fastest way to retrieve data, per the DBI docs:
[fetchrow_arrayref] Fetches the next row of data and returns a reference to an array holding the field values. Null fields are returned as undef values in the array. This is the fastest way to fetch data, particularly if used with $sth->bind_columns.
Perhaps the OP misread this as saying that the fastest option was fetchall_arrayref rather than fetchrow_arrayref?

The recommended technique, then, would be:

my $sth = $dbn->prepare("select number,id,start_dat,end_dat from SUBSC +RIBERSLIST"); $sth->execute(); $sth->bind_columns(\$number, \$id, \$start_dat, \$end_dat); my %hash =(); while ($sth->fetch) { # ->fetch populates the variables from ->bind_columns push @{$hash{$number}}, [$id, $start_dat, $end_dat]; }; $sth->finish(); $dbn->disconnect;

But, as already noted, you should also SELECT only the rows you need and do your processing line-by-line instead of sucking in the whole table at once if possible. I've only addressed the mechanics of how the OP is pulling the SELECTed rows.

Replies are listed 'Best First'.
Re^3: fastest method to use DBI
by perrin (Chancellor) on Jul 07, 2009 at 15:34 UTC
    I suspect what the OP saw was something about fetchall_arrayref bering the fastest way to fetch all the data, which is true since it doesn't require looping in perl. However, fetchall_arrayref doesn't work with bind_columns. Also, since the OP is only after a single row, there is no advantage to fetchall_arrayref.
      which is true since it doesn't require looping in perl

      Actually DBI implements fetchall_arrayref as loop in perl

        That's just a default implementation. It depends on the driver. The goal is to override it where databases allow something faster.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://777883]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (5)
As of 2021-01-20 23:52 GMT
Find Nodes?
    Voting Booth?