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
>
