Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: perl DBI question: fetchrow_array

Re: perl DBI question: fetchrow_array

From: Jared Still <jkstill_at_cybcon.com>
Date: Fri, 07 Mar 2003 08:49:10 -0800
Message-ID: <F001.005635F3.20030307084910@fatcity.com>

Thanks Tim.

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

Jared

On Friday 07 March 2003 07:09, Tim Bunce wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.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 - 10:49:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US