On Thu, Mar 06, 2003 at 02:54:19PM -0800, [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
