I've been doing some test and I have found that perl scripts are faster
than I used to thought and in some cases faster than C programs. The Perl
script 'automagically' does bulk fetches that speed up your application a
lot (well the wizard are the DBI and DBD::Oracle who do the dirty work). In
a test machine a Perl fetched 50000 rows in 7 seconds and a C program do
the same task in 20 seconds. If I set the dbh->{RowCacheSize}=1 the Perl
script takes 30 seconds.
To obtain better performance with C you have two options: you fetch the
data in arrays (very fast but you need to program a bit more) or you use
the 'prefetch' flag with the proc (this is a new feature of Oracle 8i and
has the advantage that run as fast as a C program with arrays without
changing your code). This program fetched the same amount of rows in 4
seconds. All this works only with select operations.
Best regards,
Marcelo.
"Marcotullio, Angelo " <[EMAIL PROTECTED]> on 08/06/2001 14:12:22
Please respond to "Marcotullio, Angelo " <[EMAIL PROTECTED]>
To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
cc:
Subject: RE: Data Extract
There is no commit because i'm only SELECTing the data from Oracle. I'm
writing to ASCII files.
-----Original Message-----
From: Steve Sapovits [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 08, 2001 1:02 PM
To: Curt Russell Crandall; Pettit, Chris L
Cc: '[EMAIL PROTECTED]'
Subject: RE: Data Extract
We've also found that committing in blocks speeds up heavy
inserts with Perl/DBI/Oracle. How big depends on the job.
Most people do all or nothing: AutoCommit or no commit and
a rollback or commit at the end. Something in between usually
is better for most inserts.
----
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: Curt Russell Crandall [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, June 08, 2001 12:36 PM
> To: Pettit, Chris L
> Cc: '[EMAIL PROTECTED]'
> Subject: RE: Data Extract
>
> First of all, I've never used Oracle so I'm not aware of any fancy tools
> to make life easier to accomplish this.
> From the original post, asking about optimizing Perl to do this task, I
> inferred that speed might be an issue. With Sybase, the difference
> between using Perl and the DBI versus using C and the OpenClient
libraries
> is negligible for almost everything we do... but we aren't trying to
> select and insert 1/2 billion rows. I recall overhearing a senior
> programmer at this shop remark that the difference in using C vs. Perl
for
> DB apps. amounts to less than a 10th of a millisecond per select or
insert
> (don't remember which one of if there's really a difference in speed
> between the 2 ops).
> Given your figures and the ones Michael gave, I would assume moving 1/2
> billion rows would take in the neighborhood of 7+ hours. If you could
> shave 1x10^-6 seconds off from moving each row, you'd save about an hour
> and a half. Depending on how critical time requirements are... and how
> often you'd need to do something like this... writing the code in C might
> be worth it and I wouldn't think writing the code in C would really take
a
> whole lot longer for an application like this (unless you know Perl very
> well and you know C not so well).
> Of course if Oracle came if a nice tool to simplify this, it would
> probably be faster not just to write the app but also in terms of
> execution speed.
>
> Now, if you want to optimize the Perl code, I'd just make sure to prepare
> any select/insert statement up front and in your loop just do an execute
> on the prepared statement. I've also found that committing every 100-200
> transactions (in Sybase) seems to give me the best performance.
>
> On Fri, 8 Jun 2001, Pettit, Chris L wrote:
>
> >
> > I've used Perl to prepare large files for SQL Loader.
> > 1.9 million. SQL loader is fast, I was able to load the 1.9 mill
records
> in
> > less than six minutes.
> > We had to do text extraction for the data.
> > DBI is absolutley awesome for that IMHO.
> > clp
> >
