Don't forget that Oracle caches query results. If you run the perl test first, then the SQL*PLUS test right after it, the dramatic difference may only be due to caching.
I don't know if there's an easy way to disable the caching, if there is I'd love to find out about it. -----Original Message----- From: JupiterHost.Net [mailto:[EMAIL PROTECTED] Sent: Thursday, May 12, 2005 10:33 AM To: [email protected] Subject: Re: Perl Performance Help. > a) We are using Oracle 8i DB. > The query (or the DBMS) takes only 6 seconds > to return data to Perl. How did you determine this? I think you're just assuming that. > 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 As other have said compare the CLI version of the query to the perl version: (Not an oracle person so if it looks like MySQL well, you'll live, its just an example to illustrate what to do) oracle> SELECT TIME(); # your DB's equivalent obviously oracle> YOUR QUERY; oracle> SELECT TIME(); vs. perl -mstrict -MDBI -we 'my $dbh = DBI->connect("YOURDSNHERE") or die DBI->errstr; print time(),"\n";$dbh->selectall_arrayref("YOUR QUERY");print time(),"\n";'
