fetchrow_array() vs fetchrow_arrayref() efficiency etc.

Connection is to a MSSQL Server using DBI:sybase.

queries are the same, difference is one uses fetchrow_array() and the
other uses fetchrow_arrayref().

Supposedly, fetrow_arrayref() is faster than the other but doesn't seem
that way.
(this is per Tim Bunce - Advanced DBI Tutorial July 2007)

Query obtained a total of ~70 columns. (output diverted to /dev/null)

Average of Actual Time Process Time
Fetchtype real sys user Grand Total
aray 5.3462 0.5484 3.128 3.007533333
arrayref 6.5944 0.878 2.696 3.389466667
Grand Total 5.9703 0.7132 2.912 3.1985


while ( $first = $sth->fetchrow_arrayref )
{
my $count = @$first;
for (my $counter = 0; $counter < $count; $counter = $counter + 1)
{
if ($counter == $count-1)
{
if (defined($first->[$counter]))
{
print "\"$first->[$counter]\"";
}
} else {
if (!defined($first->[$counter]))
{
print ",";
} else {
print "\"$first->[$counter]\",";
}
}
}
print "\n";
}

vs :

while ( @first = $sth->fetchrow_array )
{
my $count = @first;

for (my $counter = 0; $counter < $count; $counter = $counter + 1)
{
if ($counter == $count-1)
{
if (defined($first[$counter]))
{
print "\"$first[$counter]\"";
}
} else {
if (!defined($first[$counter]))
{
print ",";
} else {
print "\"$first[$counter]\",";
}
}
}
print "\n";
}

Re: fetchrow_array() vs fetchrow_arrayref() efficiency etc.

On Fri, 2007-09-07 at 14:33 +0800, Ow Mun Heng wrote:
> Connection is to a MSSQL Server using DBI:sybase.
>
> queries are the same, difference is one uses fetchrow_array() and the
> other uses fetchrow_arrayref().
>
> Supposedly, fetrow_arrayref() is faster than the other but doesn't seem
> that way.
> (this is per Tim Bunce - Advanced DBI Tutorial July 2007)
>
> Query obtained a total of ~70 columns. (output diverted to /dev/null)
>
> Average of Actual Time Process Time
> Fetchtype real sys user Grand Total
> aray 5.3462 0.5484 3.128 3.007533333
> arrayref 6.5944 0.878 2.696 3.389466667
> Grand Total 5.9703 0.7132 2.912 3.1985
>
>
> while ( $first = $sth->fetchrow_arrayref )
> {
> my $count = @$first;
> for (my $counter = 0; $counter < $count; $counter = $counter + 1)
> {
> if ($counter == $count-1)
> {
> if (defined($first->[$counter]))
> {
> print "\"$first->[$counter]\"";
> }
> } else {
> if (!defined($first->[$counter]))
> {
> print ",";
> } else {
> print "\"$first->[$counter]\",";
> }
> }
> }
> print "\n";
> }
>
> vs :
>
> while ( @first = $sth->fetchrow_array )
> {
> my $count = @first;
>
> for (my $counter = 0; $counter < $count; $counter = $counter + 1)
> {
> if ($counter == $count-1)
> {
> if (defined($first[$counter]))
> {
> print "\"$first[$counter]\"";
> }
> } else {
> if (!defined($first[$counter]))
> {
> print ",";
> } else {
> print "\"$first[$counter]\",";
> }
> }
> }
> print "\n";
> }
>
>

Again.. bad nettiquette but...

Found out can do DBI_profiling..


$DBI_PROFILE=1 perl mssql_fetchrowarray.pl > /dev/null
DBI::Profile: 0.834204s 16.68% (15333 calls) mssql_fetchrowarray.pl @
2007-09-07 14:36:11

$ DBI_PROFILE=1 perl mssql_fetchrowarrayref.pl > /dev/null
DBI::Profile: 1.077652s 21.55% (15333 calls) mssql_fetchrowarrayref.pl @
2007-09-07 14:36:20


'execute' => 0.249191s
'fetchrow_arrayref' => 0.800888s / 15319 = 0.000052s avg
(first 0.000633s, min 0.000010s, max 0.005775s)
'prepare' => 0.000489s

'execute' => 0.027413s
'fetchrow_array' => 0.789209s / 15319 = 0.000052s avg (first 0.001905s,
min 0.000025s, max 0.010331s)
'prepare' => 0.000216s
>

Re: fetchrow_array() vs fetchrow_arrayref() efficiency etc.

Be careful with your benchmarks. You're doing so much in the loop that
any diference between the two methods is "lost in the noise". The cost
of a print, for example, is relatively high and variable (buffering
and screen/file i/o etc).

And besides, the difference between the methods is so small that it's
only remotely relevant when fetching very large numbers of rows and
doing very little with them within the loop.

Beware premature optimization. If it's not hurting, don't "fix" it.

Tim.

On Fri, Sep 07, 2007 at 02:33:26PM +0800, Ow Mun Heng wrote:
> Connection is to a MSSQL Server using DBI:sybase.
>
> queries are the same, difference is one uses fetchrow_array() and the
> other uses fetchrow_arrayref().
>
> Supposedly, fetrow_arrayref() is faster than the other but doesn't seem
> that way.
> (this is per Tim Bunce - Advanced DBI Tutorial July 2007)
>
> Query obtained a total of ~70 columns. (output diverted to /dev/null)
>
> Average of Actual Time Process Time
> Fetchtype real sys user Grand Total
> aray 5.3462 0.5484 3.128 3.007533333
> arrayref 6.5944 0.878 2.696 3.389466667
> Grand Total 5.9703 0.7132 2.912 3.1985
>
>
> while ( $first = $sth->fetchrow_arrayref )
> {
> my $count = @$first;
> for (my $counter = 0; $counter < $count; $counter = $counter + 1)
> {
> if ($counter == $count-1)
> {
> if (defined($first->[$counter]))
> {
> print "\"$first->[$counter]\"";
> }
> } else {
> if (!defined($first->[$counter]))
> {
> print ",";
> } else {
> print "\"$first->[$counter]\",";
> }
> }
> }
> print "\n";
> }
>
> vs :
>
> while ( @first = $sth->fetchrow_array )
> {
> my $count = @first;
>
> for (my $counter = 0; $counter < $count; $counter = $counter + 1)
> {
> if ($counter == $count-1)
> {
> if (defined($first[$counter]))
> {
> print "\"$first[$counter]\"";
> }
> } else {
> if (!defined($first[$counter]))
> {
> print ",";
> } else {
> print "\"$first[$counter]\",";
> }
> }
> }
> print "\n";
> }
>
>

Re: fetchrow_array() vs fetchrow_arrayref() efficiency etc.

On Fri, Sep 07, 2007 at 03:02:09PM +0800, Ow Mun Heng wrote:
>
> On Fri, 2007-09-07 at 14:33 +0800, Ow Mun Heng wrote:
> > Connection is to a MSSQL Server using DBI:sybase.
>
> Found out can do DBI_profiling..
>
> $DBI_PROFILE=1 perl mssql_fetchrowarray.pl > /dev/null
> DBI::Profile: 0.834204s 16.68% (15333 calls) mssql_fetchrowarray.pl @ 2007-09-07 14:36:11
>
> $ DBI_PROFILE=1 perl mssql_fetchrowarrayref.pl > /dev/null
> DBI::Profile: 1.077652s 21.55% (15333 calls) mssql_fetchrowarrayref.pl @ 2007-09-07 14:36:20

Okay, now I'm more interested because DBI Profiling isolates the cost better.

> 'execute' => 0.249191s
> 'prepare' => 0.000489s
> 'fetchrow_arrayref' => 0.800888s / 15319 = 0.000052s avg (first 0.000633s, min 0.000010s, max 0.005775s)
>
> 'prepare' => 0.000216s
> 'execute' => 0.027413s
> 'fetchrow_array' => 0.789209s / 15319 = 0.000052s avg (first 0.001905s, min 0.000025s, max 0.010331s)

Note the wildly different prepare() and execute() times between the two runs.
(I'm trusting here that the query itself is the same.)

Given that level of variability (probably due to db load and/or network
issues) you can't expect to get a meaningful comparison of the two methods.

And even if you could, it should be clear that the difference is but a
tiny fraction of the overall time spent processing the query.

Tim.

Re: fetchrow_array() vs fetchrow_arrayref() efficiency etc.

On Fri, 2007-09-07 at 10:43 +0100, Tim Bunce wrote:
> On Fri, Sep 07, 2007 at 03:02:09PM +0800, Ow Mun Heng wrote:
> > 'execute' => 0.249191s
> > 'prepare' => 0.000489s
> > 'fetchrow_arrayref' => 0.800888s / 15319 = 0.000052s avg (first 0.000633s, min 0.000010s, max 0.005775s)
> >
> > 'prepare' => 0.000216s
> > 'execute' => 0.027413s
> > 'fetchrow_array' => 0.789209s / 15319 = 0.000052s avg (first 0.001905s, min 0.000025s, max 0.010331s)
>
> Note the wildly different prepare() and execute() times between the two runs.
> (I'm trusting here that the query itself is the same.)

Yes it is. But as mentioned network load etc convulates things a bit

> And even if you could, it should be clear that the difference is but a
> tiny fraction of the overall time spent processing the query.

Just wanted to ensure that I make full use of the efficiency of the
relevent pull methods are stated in your talk.

Since there's no discernible difference, I'll leave it as it is..

Thanks for the input.