(Fwd) RE: perl DBI question: fetchrow_array

[Tim Bunce]

----- Forwarded message from gmei <[EMAIL PROTECTED]> -----

Delivered-To: [EMAIL PROTECTED]
From: "gmei" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: RE: perl DBI question: fetchrow_array
Date: Fri, 7 Mar 2003 13:39:14 -0500
In-Reply-To: <[EMAIL PROTECTED]>

Hi, Tim:

Thanks for your reply. I have a couple of more-or-less baic questions of dbi
syntax.

I just checked the version of DBI on my machine:

atlas$ ls -d /usr/local/src/perl_modules/DBI*[0-9]
/usr/local/src/perl_modules/DBI-1.13

Right now we could not upgrade DBI on this production system, but I will ask
the sysadmin to consider.

With the DBI version we have, I did find that

$dat->bind_columns(undef,\($row));
while($dat->fetch) {
        print DATA "$row\n";
    }

is a bit faster than

    while(($row) = $dat->fetchrow_array) {
        print DATA "$row\n";
    }
    close(DATA);

when we have $dat = $dbh->prepare("select " . join('||chr(9)||', @select) .
" from $table");

We don't need to process the data in the result set, I just want to dump
them into a file, So I guess I don't need to use "shift" in your example? Is
"fetchall_arrayref" still the fastest? What is the syntax of it? could you
use my code above as example of how to use "fetchall_arrayref"?

Also,is $dat->fetch the same as $dat->fetchrow_arrayref?

Thanks.

Guang

> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tim Bunce
> Sent: Friday, March 07, 2003 10:09 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: perl DBI question: fetchrow_array
>
>
> 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.
>


----- End forwarded message -----