Perl Script插入Oracle数据库的速度很慢

Question

我是Perl的新手，并设法编写以下脚本将数据插入Oracle DB表以进行进程负载测试。脚本按预期工作。但问题是插入率很低。(每秒35个插入)。当尝试运行100万次插入时，它会运行数小时。有人能在脚本中找到任何可以减慢插入速度的问题吗?还是有其他问题?

多谢

#!/usr/bin/perl
$num_args = $#ARGV + 1;
if ($num_args != 2) {
    print "Usage: CashPositionInsert.pl envName noOfRecordsn";
    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";

Answer 1

如果在循环之前只准备一次语句，使用bind变量，可能会获得相当大的性能:

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 ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";

然后填写每个执行调用中的变量:

$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 );