how to get count in DBI

[Anish Kumar K.]

$rows=$dbh->selectall_arrayref("SELECT sessionid FROM session");

I want to get the count...For example if it returns three rows....Then count should be three...

How will I get the count....As soon after I want to print the count in the start itself...

Anish

[Charles K. Clarkson]

Anish Kumar K. <***@vitalect-india.com> wrote:

: $rows=$dbh->selectall_arrayref("SELECT sessionid FROM session");
:
: I want to get the count...For example if it returns
: three rows....Then count should be three...
:
: How will I get the count....As soon after I want to
: print the count in the start itself...

Well, selectall_arrayref() returns an array reference.
To get the count of an array, we use something like this.

my @fruit = qw( apple banana orange );

printf "There are %s fruits listed.\n", scalar @fruit;

__END__


We can also set a scalar variable to the fruit quantity.
We won't need 'scalar' here.

my @fruit = qw( apple banana orange );

my $fruit_count = @fruit;

# $fruit_count is now 3

__END__


To get the count of an array reference we also use the
@ prefix.

my $rows = $dbh->selectall_arrayref(
'SELECT sessionid FROM session'
);

my $row_count = @$rows;


HTH,

Charles K. Clarkson

[Ing. Branislav Gerzo]

Charles K. Clarkson [CKC], on Friday, November 26, 2004 at 04:32
(-0600) wrote these comments:


CKC>my $rows = $dbh->>selectall_arrayref(
CKC> 'SELECT sessionid FROM session'
CKC> );
CKC> my $row_count = @$rows;

isn't faster using SELECT COUNT(*) FROM session ?

[Charles K. Clarkson]

Ing. Branislav Gerzo <***@2ge.us> wrote:

: Charles K. Clarkson [CKC], on Friday, November 26, 2004 at
: 04:32 (-0600) wrote these comments:
:
:
: : my $rows = $dbh->selectall_arrayref(
: : 'SELECT sessionid FROM session'
: : );
: : my $row_count = @$rows;
:
: isn't faster using SELECT COUNT(*) FROM session ?

It would be slower and less accurate. To use your
suggestion would require another query on the database.
That count might also not return the same count as is
in "scalar @$rows" if the database is updated between
queries.


HTH,

Charles K. Clarkson

[Ing. Branislav Gerzo]

Charles K. Clarkson [CKC], on Friday, November 26, 2004 at 05:30
(-0600) has on mind:

CKC> It would be slower and less accurate. To use your
CKC> suggestion would require another query on the database.
CKC> That count might also not return the same count as is
CKC> in "scalar @$rows" if the database is updated between
CKC> queries.

hm, I did some benchmarks and it seems I'm right, I use something
like:

...
$start = new Benchmark;
for (1 .. 1000) {
$sth = $dbh->prepare_cached('SELECT COUNT(sessionid) FROM session');
$sth->execute();
$result = $sth->fetchrow_array();
$sth->finish;
}
$end = new Benchmark;

$diff = timediff($end, $start);
print "Time taken was ", timestr($diff, 'all'), " seconds\n\n";
...

and it was definitely faster, than your suggestion. Did you try some
benchmarking ?

[Ing. Branislav Gerzo]

Ing. Branislav Gerzo [IBG], on Saturday, November 27, 2004 at 15:20
(+0100) has on mind:

as I see, I should use better benchmarking:
http://uk.builder.com/webdevelopment/scripting/0,39026636,39220598-2,00.htm

but that not changes my conclusion :)

[Charles K. Clarkson]

***@stonline.sk <***@stonline.sk> wrote:

: Charles K. Clarkson [CKC], on Friday, November 26, 2004 at
: 05:30 (-0600) has on mind:
:
: : It would be slower and less accurate. To use your
: : suggestion would require another query on the database.
: : That count might also not return the same count as is
: : in "scalar @$rows" if the database is updated between
: : queries.
:
: hm, I did some benchmarks and it seems I'm right, I use
: something like:
:
: ...
: $start = new Benchmark;
: for (1 .. 1000) {
: $sth = $dbh->prepare_cached(
: 'SELECT COUNT(sessionid) FROM session' );
: $sth->execute();
: $result = $sth->fetchrow_array();
: $sth->finish;
: }
: $end = new Benchmark;
:
: $diff = timediff($end, $start);
: print "Time taken was ", timestr($diff, 'all'), " seconds\n\n";
: ...
:
: and it was definitely faster, than your suggestion. Did
: you try some benchmarking?

I timed the two ideas with logic. Here's the method I
presented.

my $rows = $dbh->selectall_arrayref(
'SELECT sessionid FROM session'
);

my $row_count = @$rows;


Here's the method you presented.

my $rows = $dbh->selectall_arrayref(
'SELECT sessionid FROM session'
);

my $row_count = $dbh->do(
'SELECT COUNT(sessionid) FROM session' );


The difference between the two methods is the second
statement.

my $row_count = @$rows;

vs.

my $row_count = $dbh->do(
'SELECT COUNT(sessionid) FROM session' );

Do you really believe that DBI will make a second
call to the database faster than perl can return the size
of an array in memory?

Now, let's say someone updates the database between
calls, on a busy web site those two queries might
accurately report different table sizes.




HTH,

Charles K. Clarkson

[Ing. Branislav Gerzo]

Charles K. Clarkson [CKC], on Saturday, November 27, 2004 at 10:21
(-0600) thinks about:

CKC> Here's the method you presented.
CKC> my $rows = $dbh->>selectall_arrayref(
CKC> 'SELECT sessionid FROM session'
CKC> );

CKC> my $row_count = $dbh->do(
CKC> 'SELECT COUNT(sessionid) FROM session' );

no we don't understand each other, why I have to put in my code:

my $rows = $dbh->>selectall_arrayref('SELECT sessionid FROM session');

isn't enough, only second SELECT?

$sth = $dbh->do('SELECT COUNT(keyword) FROM keywords');

[Charles K. Clarkson]

***@stonline.sk <***@stonline.sk> wrote:

: Charles K. Clarkson [CKC], on Saturday, November 27, 2004 at
: 10:21 (-0600) thinks about:
:
: : Here's the method you presented.
: : my $rows = $dbh->selectall_arrayref(
: : 'SELECT sessionid FROM session'
: : );
:
: : my $row_count = $dbh->do(
: : 'SELECT COUNT(sessionid) FROM session' );
:
: no we don't understand each other, why I have to put in my code:
:
: my $rows = $dbh->selectall_arrayref('SELECT sessionid FROM session');

Had he just wanted a record count, why did the OP supply
this statement? I assumed the statement had been executed and
the results used. He now wants the record count. From your
reply, I assume you interpreted his question to mean a record
count *instead of* the statement given.

We are answering two different questions. Only the OP
knows which he wants.


: $sth = $dbh->do('SELECT COUNT(keyword) FROM keywords');

Actually, do() doesn't return a statement handle ($sth).
It returns the number of rows affected ($row_count). Which is
the value we want. It is clearer to use this.

my $row_count = $dbh->do(
'SELECT COUNT(keyword) FROM keywords');




HTH,

Charles K. Clarkson