perl DBI, fastest way to get a single scalar value

Question

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?

Answer 1

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.