[Oracle-l] perl DBI question: fetchrow_array

try using fetchrow_arrayref and see if its faster or less resource
intensive.

fetchrow_arrayref is faster than fetchrow_array, as Alex has noted.

I see you've already set RowCacheSize. Anecdotal evidence ( not just
mine)
suggests that the diminished returns obtained by setting this >100 aren't
worth it.

try selectall_arrayref if you're data is not really large. 'really
large' depends
on your environment.

join DBI users list, found at lists.perl.org.

Jared

Alex
Sent by: root_at_fatcity.com
03/06/2003 01:34 PM
Please respond to ORACLE-L



To: Multiple recipients of list ORACLE-L
cc:
Subject: Re: perl DBI question: fetchrow_array

try using fetchrow_arrayref and see if its faster or less resource
intensive.

That's all true.

I'd just add that recent DBI versions let you specify a max_rows parameter
to the fetchall_arrayref method. You can then call it in a loop to get
rows on batches.

This is now the fastest way to fetch rows in a loop using the DBI:

my $rows = []; # cache for batches of rows
while( my $row = ( shift(@$rows) || # get row from cache, or reload cache:

shift(@{$rows=$sth->fetchall_arrayref(undef,10_000)||[]) )

) {
...
}

The code that implements fetchall_arrayref is written in C and,
although there's a default implementation in the DBI, a faster one
gets embedded into drivers like DBD::Oracle when it's (re)built
(after you've upgraded the DBI).

Several parts of the DBI have been optimized with this code-embedding
technique so if you've not upgraded your DBI to >= 1.29, or not
rebuilt your DBD::Oracle since then it may be worth doing so.

(FYI, if this prompts you to upgrade your DBI installation, please
note DBI 1.32 was a good release, but that 1.33 and 1.34 have problems,
including a memory leak. I hope to release a 1.35 before Monday.)

Tim.

p.s. There's some signes of life returning to DBD::Oracle these days.
Jeff Urlwin is helping me get the ball rolling again by integrating
a selection of patches from the major backlog I have. I also have a
new Solaris box and can now install Oracle 8 and 9 again...

Thanks Tim.

I'll have to play with that code a bit.

Jared

I tried "fetch" (which is same as fetchrow_arrayref):

$dat->bind_columns(undef,\($row));
while($dat->fetch) {

print DATA "$row\n";
}

And it seems the performance improved a bit (about 5%). Using RowCacheSize
or not does not seem to matter. The size of the table can vary from 1 row to
1 million rows. So I won't use fetchall_arrayref.

Thanks for your help.

Guang