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

.