Just adapt the code below and see the performance improvement.
# The corresponding Oraperl is "eval 'use Oraperl; 1';"
use DBI;
# The corresponding Oraperl is ora_login( $dbh, $instance, $userid,
$password );
# By turning off AutoCommit (ora_autocommit(0);), you avoid the overhead of
# a commit after each statement.
# RaiseErrors invokes automatic error checking
my $dbh = DBI -> connect( "dbi:Oracle:$instance", $userid, $password,
{ RaiseErrors => 1, PrintErrors => 0, AutoCommit => 0 } );
# The corresponding Oraperl call is ora_open( $dbh, $stmt),
# but note that the prepare and execute stages are separate here.
# This allows you to avoid the prepare overhead every time you
# execute the statement.
# This prepare action normally involves two round trips to the
# database to define the statement and establish the execution plan.
# Using ora_open() or ora_do() inside the loop without using bind
# variables means you get that overhead for every single row you insert.
# Having AutoCommit on means more extra roundtrips.
# The database also does more work because it can't reuse
# the execution plan from previous inserts to the table.
my $sth -> prepare( "insert into table1 ( col1, col2, col3 ) values ( ?, ?,
? )" );
while ( 1 ) {
# determine the values to insert
# The corresponding Oraperl is ora_bind( $state, $percent, $number );
# This re-executes the same statement each time,
# the only network traffic is that required to send the values.
$sth -> execute( $state, $percent, $number );
}
# The corresponding Oraperl is ora_commit( $dbh );
$dbh -> commit;
# The corresponding Oraperl is ora_logoff( $dbh );
$dbh -> disconnect;
I am using DBI calls because all new code should use them. There are
corresponding calls in Oraperl.pm, but I recommend getting used to the DBI
calls. You can intermix them because Oraperl is just a thin wrapper around
the DBI calls. The DBI calls give you much better control over execution
and error handling.
--
Mac :})
** I may forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Ted X Wang" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, January 26, 2001 2:01 PM
Subject: Re: Oraperl, DBI,DBD , ARRAY PROCESSING and performance issue
> Could you explains to me what the two extra round trip are for ?
>
> In my application, I do something like this:
>
> $sqlstmt="insert into table1 values( 'NY', 0.75,10000);
> ora_do($sqlstmt) ;
# Where's the $lda argument?
> ---------------------------------------- Message
History ------------------------------------
>
> From: [EMAIL PROTECTED] on 01/26/2001 08:57 PM GMT
>
> To: Ted X Wang/NewYork/DBNA/DeuBa@DBNA; [EMAIL PROTECTED]
> cc:
> Subject: Re: Oraperl, DBI,DBD , ARRAY PROCESSING and performance issue
>
> Array processing is not yet available, but don't underestimate the savings
> available by preparing outside the loop and using bind variables. If you
> don't do that, each time you execute the SQL, there are at least two extra
> round trips on the network that could be avoided to describe and prepare
the
> SQL.
> ----- Original Message -----
> From: "Ted X Wang" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Friday, January 26, 2001 10:51 AM
> Subject: Re: Oraperl, DBI,DBD , ARRAY PROCESSING and performance issue
>
> > My program didn't use any bind variable, prepared statement.
> > The BIND variable and prepared stmt definitely will speed up the
program,
> but without ARRAY proceccing,
> > each sql*net trip takes about 50 ms.
> >
> > The best way to speed it up is to combine the trip using the ARRAY
> processing feature.
> > Is this feature availible in the new DBI,DBB perl ?
