Perl Script Oracle DB insert rate is very slow

Question

I am new to Perl and managed to write below script to insert data to an Oracle DB table for a process load test. Script is working as expected. But the problem is insert rate is quite low. (35 insertions per second). When tried to run for 1000 000 insertions it runs for hours and hours. Can someone please find any issue in the script which can be slowing down the insert rate? Or can it be due to any other problem?

Many Thanks

#!/usr/bin/perl

$num_args = $#ARGV + 1;
if ($num_args != 2) {
    print "Usage: CashPositionInsert.pl envName noOfRecords\n";
    exit;
}

use strict;
use warnings;

use DBI;

my $dbname   = $ARGV[0];
my $dsn      = "dbi:Oracle:$dbname";
my $user     = $ARGV[0];
my $password = $ARGV[0];

my $recordCount = $ARGV[1];

my $dbh = DBI->connect($dsn, $user, $password, {
   PrintError       => 1,
   RaiseError       => 1,
   AutoCommit       => 0,
});

my $rndmizor = 4;
my $sth;

for (my $i = 1; $i <= $recordCount; $i++) {
        my $var = ($i%$rndmizor)+1;
        my ($routingSeq, $origin, $positionAccID, $publishDate, $transActID, $settlementDate, $sourceID, $settlementCurrency, $sodSnapshot, $recordStatus);
        my ($balanceType, $latest, $currentValue, $changedValue, $reference, $bankCode, $TxRef, $lastTxDateTime, $balanceSubType, $positionID, $secAccID, $location, $purpose);
        my $updateSequence;

    $routingSeq = $i;
    $origin = 6;
    $positionAccID = "Cash$var";
    $publishDate = "2015/09/23";
    $transActID = 0;
    $settlementDate = "2015/09/23";
    $sourceID = 6;
    $settlementCurrency = "SGD";
    $sodSnapshot = 1;
    $recordStatus = 1;
    $balanceType = int(rand(6))+1;
    $latest = 1;
    $currentValue = 10.1+$i*10;
    $changedValue = 9.9;
    $reference = "cashTest_$i";
    $bankCode = "OCB";
    $TxRef = "cashLoadTest_$i";
    $lastTxDateTime = "20150921";
    $balanceSubType = int(rand(8))+1;
    $positionID = "CASH-$i";
    $secAccID = "";
    $location = int(rand(5))+1;
    $purpose = int(rand(3))+1;
    $updateSequence = 0;

    my $stmt = "INSERT INTO ATSD_MOB_CASH_POSITION ( ROUTING_SEQ, ORIGIN, POSITION_ACCOUNT_ID, PUBLISH_DATE,
    TRANSACTION_ID, SETTLEMENT_DATE, SOURCE_ID, SETTLEMENT_CURRENCY, SOD_SNAPSHOT, RECORD_STATUS,
    CASH_BALANCE_TYPE, LATEST, CURRENT_VALUE, CHANGED_VALUE, REFERENCE, BANK_CODE,
    TRANSACTION_REFERENCE, LAST_TRANSACTION_DATE_TIME, CASH_BALANCE_SUB_TYPE, POSITION_ID,
    SECURITIES_ACCOUNT_ID, CASH_LOCATION, CASH_PURPOSE, UPDATE_SEQUENCE ) VALUES ( 
    $routingSeq, $origin, \'$positionAccID\', \'$publishDate\', $transActID, \'$settlementDate\', $sourceID, \'$settlementCurrency\', $sodSnapshot, $recordStatus,
    $balanceType, $latest, $currentValue, $changedValue, \'$reference\', \'$bankCode\', \'$TxRef\', \'$lastTxDateTime\', $balanceSubType, \'$positionID\', \'$secAccID\', $location, $purpose,
    $updateSequence)";

    # Prepare and execute the SQL query 
    $sth = $dbh->prepare($stmt);
    $sth->execute;

}

$sth->finish();
$dbh->disconnect();
print "Success!\n";

different prepare statement for each insert ? or just the one ? how much time its taking when you are firing insert statements in oracle directly ? — Pravin Satav
– Pravin Satav, Commented Sep 24, 2015 at 6:57
I would rather generate 100 statements, begin transaction, do insert, commit. Are you using prepared statements? I don't see any part where you use some array or map and you are running prepare in loop. Is it OK? — Ginden
– Ginden, Commented Sep 24, 2015 at 7:01
Separate prepare for each insert takes time. Thanks for pointing out. Using prepare only once helped me to improve the rate a bit. — Leni
– Leni, Commented Sep 24, 2015 at 10:10

Hellmar Becker · Accepted Answer · 2015-09-24 07:56:53Z

3

You might gain quite some performance if you prepare the statement only once before the loop, using bind variables like so:

my $stmt = "INSERT INTO ATSD_MOB_CASH_POSITION ( ROUTING_SEQ, ORIGIN, POSITION_ACCOUNT_ID, PUBLISH_DATE,
    TRANSACTION_ID, SETTLEMENT_DATE, SOURCE_ID, SETTLEMENT_CURRENCY, SOD_SNAPSHOT, RECORD_STATUS,
    CASH_BALANCE_TYPE, LATEST, CURRENT_VALUE, CHANGED_VALUE, REFERENCE, BANK_CODE,
    TRANSACTION_REFERENCE, LAST_TRANSACTION_DATE_TIME, CASH_BALANCE_SUB_TYPE, POSITION_ID,
    SECURITIES_ACCOUNT_ID, CASH_LOCATION, CASH_PURPOSE, UPDATE_SEQUENCE ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";

Then fill in the variables in each execute call:

$sth->execute ( $routingSeq, $origin, "\'$positionAccID\'", "\'$publishDate\'", $transActID, "\'$settlementDate\'", $sourceID, "\'$settlementCurrency\'", $sodSnapshot, $recordStatus,
    $balanceType, $latest, $currentValue, $changedValue, "\'$reference\'", "\'$bankCode\'", "\'$TxRef\'", "\'$lastTxDateTime\'", $balanceSubType, "\'$positionID\'", "\'$secAccID\'", $location, $purpose,
    $updateSequence );

answered Sep 24, 2015 at 7:56

Hellmar Becker

3,05215 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Wernfried Domscheit Over a year ago

You don't have to quote/escape anything. $sth->execute ( $routingSeq, $origin, $positionAccID, ... works also.

Wernfried Domscheit Over a year ago

For DATE values you should better do it like this VALUES (... TO_DATE(?, 'YYYY/MM/DD'), ...)

Leni Over a year ago

Thanks all for your answers. Performance was improved from 35 to 100 inserts per second after preparing the statement only once with bind variables. I would still like to improve it up to 1000 at least. Any suggestions?

Wernfried Domscheit Over a year ago

Does the table have any triggers?

Leni Over a year ago

Disabled table trigger and now its working with insert rate more than 1000.

Collectives™ on Stack Overflow

Perl Script Oracle DB insert rate is very slow

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related