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.
