Oracle-L: Re: perl DBI question: fetchrow

From: Tim Bunce <Tim.Bunce_at_pobox.com>
Date: Fri, 07 Mar 2003 07:09:08 -0800
Message-ID: <F001.005633C6.20030307070908@fatcity.com>

On Thu, Mar 06, 2003 at 02:54:19PM -0800, Jared.Still_at_radisys.com wrote:
> 1) fetchrow_arrayref is faster than fetchrow_array, as Alex has noted.
>
> 2) 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.
>
> 3) try selectall_arrayref if you're data is not really large. 'really
> large' depends on your environment.
>
> 4) join DBI users list, found at lists.perl.org.

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...

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Bunce
  INET: Tim.Bunce_at_pobox.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Mar 07 2003 - 09:09:08 CST

Re: perl DBI question: fetchrow_array