Hello Divya:
How does your perl program execution time compare to
using sqlplus to return all the data to a file?
regards,
David
--- Divya <[EMAIL PROTECTED]> wrote:
>
> 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?
>
