CODEWITHSUNDEEP
mysqlperldbi
Claude
Claude

Reputation: 171

perl DBI, fastest way to get a single scalar value

I have this code to get a value count.

Short way:

my $count = $dbh->selectrow_array("SELECT COUNT(name) AS RESCOUNT FROM users");

Long way

my $sth = $dbh->prepare("SELECT COUNT(name) AS RESCOUNT FROM users");
$sth->execute() or die "$DBI::errstr";
my $count = $sth->fetchrow_array();
$sth->finish;

selectrow_array, fetchrow_array --> but I don't need an array. I checked the docs, but found nothing for scalars. Just methods for arrays and hashes. The method I use is fast enough, but I was just curious if there is a better, fastest way to get a single value from the call. Or this is the fastest possible way?

Upvotes: 5

Views: 1979

Answers (2)

Bruce Van Allen
Bruce Van Allen

Reputation: 177

If "modern" means "I only heard of it recently", I'm feeling all modern with DBI's bind_col and bind_columns. Cribbing from a post by DBI hero Tim Bunce...

For your case:

my $sth = $dbh->prepare("SELECT COUNT(name) AS RESCOUNT FROM users");
my $count = 0;
$sth->bind_col(1,\$count);  # bind to a reference to the variable
$sth->execute() or die "$DBI::errstr";
$sth->fetch;
print $count;

In a loop for a SELECT statement returning multiple records:

my $sth = $dbh->prepare(qq{SELECT name FROM users WHERE zip_code == '20500'});
my $name = '';
$sth->bind_col(1,\$name);  # bind to a reference to the variable
$sth->execute() or die "$DBI::errstr";
while ($sth->fetch) {
    print $name, "\n";
}

And with bind_columns this works:

my $sth = $dbh->prepare(qq{SELECT name,phone,address FROM users WHERE zip_code == '20500'});
my @fields = qw/name phone address/; 
# With a 'SELECT All * ...', get all columns with @{$sth->{NAME_lc}}
my %data;
$sth->bind_columns( \( @data{@fields} ) ); # \(...) gives references to its elements
$sth->execute() or die "$DBI::errstr";
while ($sth->fetch) {
    print "$data{name} lives at $data{address}, with phone $data{phone}.", "\n";
}

Once the setup is handled, the looping is simple to write and fast to run. (But, benchmark).

HTH, apologize if this diverges too much from the OP's problem statement. But it's the simplest and most direct way to get your returned data into the form of variable(s) you want, so you can move on to doing something with it...

Upvotes: 1

simbabque
simbabque

Reputation: 54333

The fastest way is to use fetchrow_arrayref or selectrow_arrayref, depending on how many executes you have. This only really makes a difference if executed in a loop and you have thousands (or rather hundreds of thousands) of rows.

When using fetchrow_array, it will make a copy every time, which slows you down. Also keep in mind that the behaviour for scalar context is only partly defined.

If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that.

You can also do bind_col, which works with references.

There used to be a good presentation on DBI speeds from about 10 or more years ago that I can't find right now. Also take a look at this very old Perlmonks post, that explains quite a bit about performance.

Keep in mind that you should only do optimisation when you really know you need it. Most of the time you won't.

Upvotes: 2

Related Questions