Hi Jonathan,
Here are some of the statistics :
a) We are using Oracle 8i DB. The query (or the DBMS) takes only 6 seconds
to return data to Perl.
b) Initially we thought that push @records, [ @each_record ] in the below
loop is taking time. But even if we comment it, the time taken by the loop
remains same.
while ( @each_record = $stmt_handle->fetchrow)
{
#push @records, [ @each_record ] ;
}
c) So we conclude that it is mainly an issue with fetchrow especially when
the number of records are high. We tried other possible options like
$stmt_handle->fetch()
$stmt_handle->bind_columns()
$stmt_handle->fetchall_arrayref()
$stmt_handle->fetchrow_array()
$stmt_handle->fetchall_hashref()
@records = @{ $db_handle->selectall_arrayref($sql_stmt) };
But no luck there too!
d) Due to some policy issues, we cannot have mod_perl installed on the
server.
Can you please suggest if we have more options.
Thanks & rgds
-Divya
-----Original Message-----
From: Jonathan Leffler [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 12, 2005 6:01 PM
To: Divya
Cc: [email protected]
Subject: Re: Perl Performance Help.
On 5/12/05, Divya <[EMAIL PROTECTED]> wrote:
> I am in a Perl based project, where we fire a query which returns 20K
> records.
> In the perl side, we use the following snippet to get the query result to
an
> array.
>
> while ( @each_record = $stmt_handle->fetchrow)
>
> {
> push @records, [ @each_record ] ;
> }
>
> Upon analysis, we found that "$stmt_handle->fetchrow" is the one taking
> around 2 minutes.
...
> Has anybody come across, similar scenario. Is there any other way that
will
> take less time to fetch the data and assign to an array.
You don't say which DBMS you're using - not that it matters very much.
If the time is all in the fetchrow, then the question is "how much
fast than 166 rows per second can the DBMS get data to the application
without Perl + DBI + DBD::WhatEver"? For example, are you sure you
need all the columns you're selecting, or would less data do? Are the
query criteria complex and taking the time? How much of the 2 minutes
is spent in Perl and how much is spent waiting for the DBMS to send
Perl the data?
