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.
Any opinions expressed in this E-mail may be those of the individual and not necessarily the company. This E-mail and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that you have received this E-mail in error and that any use or copying is strictly prohibited. If you have received this E-mail in error please notify the beCogent postmaster at [EMAIL PROTECTED] Unless expressly stated, opinions in this email are those of the individual sender and not beCogent Ltd. You must take full responsibility for virus checking this email and any attachments. Please note that the content of this email or any of its attachments may contain data that falls within the scope of the Data Protection Acts and that you must ensure that any handling or processing of such data by you is fully compliant with the terms and provisions of the Data Protection Act 1984 and 1998.
