FAQ
I have some perl code which selects table data and write it into a file. I
have something like:

$dbh->{RowCacheSize} = 10000;
open(DATA, ">$tn") || die "Can't open file\n";
$dat=$dbh->prepare("select id||chr(9)||FUNCTIONID||chr(9)||GENEID from
FUNCTION2GENE");

$dat->execute();
while(($row) = $dat->fetchrow_array) {

print DATA "$row\n";
}
close(DATA);

I am trying to see if there is any way to speed up the process.

So here is my question:

Is "fetchrow_array" the fatest way to get the data?

TIA.

Guang

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: gmei
INET: gmei_at_incyte.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).

Search Discussions

  • Alex at Mar 6, 2003 at 9:34 pm
    try using fetchrow_arrayref and see if its faster or less resource
    intensive.
    On Thu, 6 Mar 2003, gmei wrote:

    I have some perl code which selects table data and write it into a file. I
    have something like:

    -------
    $dbh->{RowCacheSize} = 10000;
    open(DATA, ">$tn") || die "Can't open file\n";
    $dat=$dbh->prepare("select id||chr(9)||FUNCTIONID||chr(9)||GENEID from
    FUNCTION2GENE");
    $dat->execute();
    while(($row) = $dat->fetchrow_array) {
    print DATA "$row\n";
    }
    close(DATA);
    -----

    I am trying to see if there is any way to speed up the process.

    So here is my question:

    Is "fetchrow_array" the fatest way to get the data?

    TIA.
    Guang



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: gmei
    INET: gmei_at_incyte.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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Alex
    INET: axs_at_m-net.arbornet.org

    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).
  • Jared.Still_at_radisys.com at Mar 6, 2003 at 10:54 pm
    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.
    On Thu, 6 Mar 2003, gmei wrote:

    I have some perl code which selects table data and write it into a file. I
    have something like:

    -------
    $dbh->{RowCacheSize} = 10000;
    open(DATA, ">$tn") || die "Can't open file\n";
    $dat=$dbh->prepare("select id||chr(9)||FUNCTIONID||chr(9)||GENEID from
    FUNCTION2GENE");
    $dat->execute();
    while(($row) = $dat->fetchrow_array) {
    print DATA "$row\n";
    }
    close(DATA);
    -----

    I am trying to see if there is any way to speed up the process.

    So here is my question:

    Is "fetchrow_array" the fatest way to get the data?

    TIA.
    Guang



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: gmei
    INET: gmei_at_incyte.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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Alex
    INET: axs_at_m-net.arbornet.org

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

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author:
    INET: Jared.Still_at_radisys.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).
  • Tim Bunce at Mar 7, 2003 at 3:09 pm

    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).
  • Jared Still at Mar 7, 2003 at 4:49 pm
    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).
  • Gmei at Mar 7, 2003 at 8:19 pm
    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
    -----Original Message-----
    From: root_at_fatcity.com On Behalf Of
    Jared.Still_at_radisys.com
    Sent: Thursday, March 06, 2003 5:54 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: perl DBI question: fetchrow_array


    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.

    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.


    On Thu, 6 Mar 2003, gmei wrote:

    I have some perl code which selects table data and write it
    into a file.
    I
    have something like:

    -------
    $dbh->{RowCacheSize} = 10000;
    open(DATA, ">$tn") || die "Can't open file\n";
    $dat=$dbh->prepare("select
    id||chr(9)||FUNCTIONID||chr(9)||GENEID from
    FUNCTION2GENE");
    $dat->execute();
    while(($row) = $dat->fetchrow_array) {
    print DATA "$row\n";
    }
    close(DATA);
    -----

    I am trying to see if there is any way to speed up the process.

    So here is my question:

    Is "fetchrow_array" the fatest way to get the data?

    TIA.
    Guang



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: gmei
    INET: gmei_at_incyte.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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Alex
    INET: axs_at_m-net.arbornet.org

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




    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author:
    INET: Jared.Still_at_radisys.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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: gmei
    INET: gmei_at_incyte.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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 6, '03 at 9:03p
activeMar 7, '03 at 8:19p
posts6
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase