For large fetches, where I have lots of RAM, I set this
much higher than 500 -- I try to match the size of the
result set so there's a single network trip. It makes a
difference -- at the expense of memory of course.
Is the example below accurate in terms of the fetch loop?
i.e., have your really removed all the code in that loop
for comparison purposes? 9 times out of 10 it's what's
in my fetch loop that adds overhead -- not DBI.
Set RowCacheSize to the number of rows you expect to get
back and I bet you see a difference.
----
Steve Sapovits
Global Sports Interactive
Work Email: [EMAIL PROTECTED]
Home Email: [EMAIL PROTECTED]
Work Phone: 610-491-7087
Cell: 610-574-7706
Pager: 877-239-4003
> -----Original Message-----
> From: Steve Baldwin [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, March 14, 2002 6:10 PM
> To: Chris Starling
> Cc: [EMAIL PROTECTED]
> Subject: Re: Improving DBD::Oracle Performance?
>
> You could try setting the RowCacheSize to something like 500, so the
> round trips to the DB are reduced, and it effectively does array
> fetches. This is covered (I think) in the DBI POD.
>
> Chris Starling wrote:
>
> >First question: Is there a DBD::Oracle specific mailing list I should
> >be posting this question to?
> >
> >Now onto my real question:
> >
> >
> > Is there some way to tweak DBD::Oracle or DBI to make it run faster?
> >
> >
> >I'm exporting a large amount of data from Oracle 8.1.7 with a Perl
> >script using DBD::Oracle, and I'm getting pretty lousy performance.
> >I've eliminated most variables by running my perl script and then
> >running a SQL*Plus script using the same query. I've done this on
> >three different platforms now and the SQL*Plus script is MANY, MANY
> >times faster. The Perl runs on the order of 200 seconds and the
> >SQL*Plus is usually between 25 and 40 seconds. The query itself runs
> >in less than 10 seconds.
> >
> >Platforms tested:
> >
> >Linux 2.4.18 (Redhat 7.1 I think)
> >perl 5.6.1
> >DBI 1.14
> >DBD::Oracle 1.06
> >
> >CygWin on Win2K
> >perl 5.6.1
> >DBI 1.20
> >DBD::Oracle 1.12
> >
> >Solaris 7
> >Unsure of perl & DBI versions (not my box)
> >
> >
> >Below is the Perl that I'm testing. I've removed the SQL and column
> >names to protect the innocent:
> >
> >
> >
> >#!/usr/bin/perl
> >use strict;
> >use DBI;
> >
> >my $dbinstance = "dbinstance";
> >my $dbuser = "dbuser";
> >my $dbpasswd = "dbpasswd";
> >my $sql = "query that returns many rows";
> >
> >my $dbh = DBI->connect("dbi:Oracle:$dbinstance", $dbuser, $dbpasswd);
> >my $sth = $dbh->prepare($sql);
> >$sth->execute;
> >my ($col1, $col2, $col3, $col4);
> >$sth->bind_columns( \($col1, $col2, $col3, $col4) );
> >while ($sth->fetch) {}
> >
> >
> >
> >Not much to it, really.
> >
> >Note that I'm not doing anything with each row I fetch. The SQL*Plus
> >script is "SPOOL"-ing to a file, so even with the disk overhead it's
> >still faster. (alas!)
> >
> >I've been running both Perl & SQL*Plus on both a machine with a local
> >oracle instance and a machine where the oracle is across the network on
> >another machine. It seemed to make little impact and the SQL*Plus was
> >still significantly faster regardless of the situation.
> >
> >So unless there are any other variables or factors that I'm missing
> >here, I'm concluding that the difference in speed has to do with the
> >internal workings of DBI and DBD::Oracle. So the question is:
> >
> >
> >Is there some way to tweak DBD::Oracle or DBI to make it run faster?
> >
> >
> >Thanks!
> >-Chris Starling
> >
>
