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
