Thx for all the answers.
Well, you're right. I check for error against DB handle, not against
statement.
But that's just a typo. The statement itself works.
The problem is within the loop:
> while ( @row = $Statement->fetchrow_array ) {
> warn "@row\n";
> }
If I rewrite this to
print "Here we start...\n";
> while ( @row = $Statement->fetchrow_array ) {
> warn "@row\n";
> }
print "...and here it ends\n";
you'll see the 300+ @row lines of the query in a few seconds printed, but
"...and here it ends" is printed a couple of hours (!) later.
To get the results it's the same response time as from being executed via
sqlplus.
I'll trace it and see what I'll get.
Thx so far,
Olly
> -----Original Message-----
> From: Gaul, Ken [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 01, 2004 2:08 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: RE: fetchrow_array performance
>
>
> Are you sure the query only takes a few seconds and not that it only
> takes a few seconds to return the rows that makes your selection
> criteria. Not sure how big your database is but for anything
> other than
> trivial data volumes I would say that performance will be very poor.
> (obviously that is just an opinion of somebody who has no information
> about your DB and what you are trying to achieve!)
> How long does the query take if you run it from SQL*Plus (ie. How long
> before you get a prompt back). What does the execution plan look like?
>
> Ken.
>
> --
> IT Infrastructure Manager
> beCogent Ltd
> T: +44 1236 628140
> E: [EMAIL PROTECTED]
>
> -----Original Message-----
> From: Reidy, Ron [mailto:[EMAIL PROTECTED]
> Sent: 01 December 2004 12:52
> To: Hardy Merrill; [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: RE: fetchrow_array performance
>
> I agree - set RasieError. But even more, it is impossible to
> tell what
> is going on in the database without a 10046 trace.
>
> After connecting, issue "alter session set events '10046 trace name
> context forever, level 8'. This will create a trace file in the
> directory defined by 'user_dump_dest'. Find the correct file and run
> tkprof against it. Work with your DBA on finding the cause of the
> slowdown from the trace file.
>
> --
> Ron Reidy
> Lead DBA
> Array BioPharma, Inc.
>
> -----Original Message-----
> From: Hardy Merrill [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 01, 2004 5:24 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: fetchrow_array performance
>
>
> Sorry for the top post - old version of Groupwise at work :-(
>
> You aren't error checking your dbi statements - do you have RaiseError
> set to "on" (1)? If you don't, check each dbi statement like this:
>
> my $Statement = $Database->prepare($SQL)
> or die("Prepare died: $DBI::errstr");
>
> Read about error checking in the perldocs by doing 'perldoc DBI' at a
> command prompt.
>
> *** I don't understand your problem. Seem like you are
> saying that the
> script finishes right after it prints the last row - how is
> that wrong?
> How would you expect it to finish any sooner? The fact that
> it may take
> hours to print all the rows selected doesn't matter, does it?
> Printing
> is inherently slow, in comparison to the small amount of time it takes
> to loop through all the rows without printing them.
>
> I'm probably just not understanding your problem - I don't understand
> why you are surprised that it takes a long time to print all the rows
> selected.
>
> Hardy Merrill
>
>
> >>> Schoenwaelder Oliver <[EMAIL PROTECTED]> 12/01/04
> 07:07AM >>>
> Hi,
>
> I've got a problem with a simple fetch which just takes hours
> (literaly) to
> complete.
> It's a simple select statement which, in the current case, returns
> about 300
> rows within seconds.
> But the loop is finished a couple of hours later then the last result
> is
> printed.
> Here's a code snippet:
>
> my $SQL = "select oh_deliverynumber, ol_linereference,
> oh.batchrunidentifier, oh.transactiondate
> from orderheader oh, orderline ol
> where ol_linereference not in (select
> sc_linereference
> from
> shipconfirm)
> and oh_deliverynumber=ol_deliverynumber
> and
> to_date(oh.transactiondate,'YYYYMMDDHH24MISS')+8<=sysdate";
>
> my $Statement = $Database->prepare($SQL);
>
> if (defined($Database->errstr)) {
> $Rollback=1;
> warn $Database->errstr;
> }
>
> $Statement->execute();
> if (defined($Statement->errstr)) {
> $Rollback=1;
> warn $Statement->errstr;
> }
> if (defined($Statement->fetchrow_array)) {
> $Rollback=1;
> warn $TextOnResult;
> my @row;
> while ( @row = $Statement->fetchrow_array ) {
> warn "@row\n";
> }
> }
>
> So for me it looks like the fetchrow_array function doesn't notice
> that
> there are no more rows to fetch.
> But I don't know whether it's my database, the DBD::Oracle module or
> DBI
> itself.
> Has anyone experienced the same?
> Any other idea?
>
> DBI: 1.45
> DBD::Oracle 1.16
> Oracle client: 8.1.7.0
> Database: 8.1.7.2
>
> Best regards,
>
> Olly
>
>
>
> This electronic message transmission is a PRIVATE communication which
> contains
> information which may be confidential or privileged. The
> information is
> intended
> to be for the use of the individual or entity named above. If you are
> not the
> intended recipient, please be aware that any disclosure, copying,
> distribution
> or use of the contents of this information is prohibited.
> Please notify
> the
> sender of the delivery error by replying to this message, or
> notify us
> by
> telephone (877-633-2436, ext. 0), and then delete it from your system.
>
>
>
