Re^2: fastest method to use DBI

[dsheroh]

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.

[perrin]

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.

[zwon]

which is true since it doesn't require looping in perl

Actually DBI implements fetchall_arrayref as loop in perl

[perrin]

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