in reply to Re: DBD::Oracle insert row performance
in thread DBD::Oracle insert row performance

hi marto,

Please find below my tiny SQL script, perl sample script and also C++/otlv4 sample code.

In my environment the perl script for 10000 rows runs about 5secs for each insertion mode except the third one (testins3). testins3 calls execute_array method that uses Oracle OCI's array interface DBD::Oracle and runs in a split second. But this is a different business. The C++ code runs in a split second too. It also based on OCI calls. Of course, C++ code must be faster than its perl equivalent but not that extent. I also played with database handler parameters (see the perl code below) without any kind of success.

Now, my consideration is that the reason of this poor performance of the perl script is unnecessary and/or inappropriate OCI calls in DBD::Oracle. Unfortunately I am not an Oracle OCI interface expert. BTW, I have never seen such an ugly database API like OCI. I try to understand how otlv4 (http://otl.sourceforge.net/otl3_intro.htm) is implemented and compare it to oci8.c and dbdimp.c in module DBD::Oracle with no success until now. otlv4 does not call OCIBindDynamic at all and use OCI_DEFAULT mode instead of DATA_AT_EXEC.

Simple SQL to create table 
create table speedtest (
        id integer,
        name varchar2(16),
        aod date,
        curr1 varchar2(6),
        price1 number,
        curr2 varchar2(6),
        price2 number
);

[download]
perl script 
#!/usr/local/bin/perl

use strict;
use warnings;
use feature qw/say/;
use Data::Dumper;
use Time::HiRes qw/gettimeofday tv_interval/;
use DBI;

#--------------------------------------------------------
sub rowcnt
{
    my $dbh  = shift;

    my $rowcnt = $dbh->selectrow_array('select count(*) from speedtest
+');

    return $rowcnt;
}
#--------------------------------------------------------
sub gencurr
{
    my @set = 'A' .. 'F';
    return join '' => map $set[rand @set], 1 .. 3;
}
#--------------------------------------------------------
sub genstr
{
    my $len = shift;

    return sprintf("%08X", rand(0x7fffffff));
}
#--------------------------------------------------------
sub gennum
{
    return sprintf("%08d", rand(0x7fffffff));
}
#--------------------------------------------------------
sub gendate
{
    #     0    1    2     3     4    5     6     7     8
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
            localtime(rand(0xffffffff));

    return sprintf("%04d-%02d-%02d", $year+1900, $mon+1, $mday);
}
#--------------------------------------------------------
sub genreal
{
    return sprintf("%10.6f", rand(0xffffffff));
}
#--------------------------------------------------------
sub gendata
{
    my $count = shift;

    my $d;

    for(my $i=0; $i<$count; ++$i) {

        $d->{$i}->{id}     = gennum();
        $d->{$i}->{name}   = genstr();
        $d->{$i}->{aod}    = gendate();
        $d->{$i}->{curr1}  = gencurr();
        $d->{$i}->{price1} = genreal();
        $d->{$i}->{name}   = genstr();
        $d->{$i}->{curr2}  = gencurr();
        $d->{$i}->{price2} = genreal();
   }

   return $d;
}
#--------------------------------------------------------
sub testins1
{
    my $dbh  = shift;
    my $data = shift;

    say 'positional bind_param and execute by row';

    my $isql = q{
        insert /*+ append */ into speedtest
            (id, name, aod, curr1, price1, curr2, price2)
            values
            (?, ?, ?, ?, ?, ?, ?)
    };

    my $isth = $dbh->prepare($isql);

    for my $i (sort keys %$data) {
        $isth->bind_param(1, $data->{$i}->{id});
        $isth->bind_param(2, $data->{$i}->{name});
        $isth->bind_param(3, $data->{$i}->{aod});
        $isth->bind_param(4, $data->{$i}->{curr1});
        $isth->bind_param(5, $data->{$i}->{price1});
        $isth->bind_param(6, $data->{$i}->{curr2});
        $isth->bind_param(7, $data->{$i}->{price2});
        $isth->execute;
    }

    $isth->finish;
    $dbh->commit;
}
#--------------------------------------------------------
sub testins2
{
    my $dbh  = shift;
    my $data = shift;

    say 'positional execute and execute by row';

    my $isql = q{
        insert /*+ append */ into speedtest
            (id, name, aod, curr1, price1, curr2, price2)
            values
            (?, ?, ?, ?, ?, ?, ?)
    };

    my $isth = $dbh->prepare($isql);

    my @fields = qw/id name aod curr1 price1 curr2 price2/;
    for my $i (sort keys %$data) {
        $isth->execute(@{$data->{$i}}{@fields});
    }

    $isth->finish;
    $dbh->commit;
}
#--------------------------------------------------------
sub testins3
{
    my $dbh  = shift;
    my $data = shift;

    say 'positional execute array';

    my $isql = q{
        insert /*+ append */ into speedtest
            (id, name, aod, curr1, price1, curr2, price2)
            values
            (?, ?, ?, ?, ?, ?, ?)
    };

    my $isth = $dbh->prepare($isql);

    my $id     = [map { $data->{$_}->{id}     } sort keys %$data];
    my $name   = [map { $data->{$_}->{name}   } sort keys %$data];
    my $aod    = [map { $data->{$_}->{aod}    } sort keys %$data];
    my $curr1  = [map { $data->{$_}->{curr1}  } sort keys %$data];
    my $price1 = [map { $data->{$_}->{price1} } sort keys %$data];
    my $curr2  = [map { $data->{$_}->{curr2}  } sort keys %$data];
    my $price2 = [map { $data->{$_}->{price2} } sort keys %$data];

    my $t0 = [gettimeofday];
    my $rv = $isth->execute_array({ ArrayTupleStatus => \my @tuple_sta
+tus },
            $id, $name, $aod, $curr1, $price1, $curr2, $price2);
    my $elapsed = tv_interval($t0, [gettimeofday]);
    say "\texecute_array takes $elapsed sec, $rv rows";
    my @err = grep {$_ != -1} @tuple_status;
    say "\terr: @err";

    $isth->finish;
    $dbh->commit;
}
#--------------------------------------------------------
sub testins4
{
    my $dbh  = shift;
    my $data = shift;

    say 'bind_param by name and execute by row';

    my $isql = q{
        insert /*+ append */ into speedtest
            (id, name, aod, curr1, price1, curr2, price2)
            values
            (:id, :name, :aod, :curr1, :price1, :curr2, :price2)
    };

    my $isth = $dbh->prepare($isql);

    for my $i (sort keys %$data) {
        $isth->bind_param(':id',     $data->{$i}->{id});
        $isth->bind_param(':name',   $data->{$i}->{name});
        $isth->bind_param(':aod',    $data->{$i}->{aod});
        $isth->bind_param(':curr1',  $data->{$i}->{curr1});
        $isth->bind_param(':price1', $data->{$i}->{price1});
        $isth->bind_param(':curr2',  $data->{$i}->{curr2});
        $isth->bind_param(':price2', $data->{$i}->{price2});
        $isth->execute;
    }

    $isth->finish;
    $dbh->commit;
}
#========================================================
$Data::Dumper::Sortkeys = 1;
my $dbname = '???';
my $user   = '???';
my $passwd = '???';
my $count = $ARGV[0] // 1;
my $verbose = 0;

$| = 1;

my $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd,
        {
            AutoCommit => 0,
            RaiseError => 1,
            RowCacheSize => 10000,
            ora_array_chunk_size => 100000,
            #ora_check_sql => 0,
            #ora_verbose => 0xff,
            #ora_oci_success_warn => 1,
        }
);

print 'DBI version: ', $DBI::VERSION, "\n";
print 'DBD version: ', $DBD::Oracle::VERSION, "\n";

$dbh->do("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'"
+);
$dbh->do("alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI
+:SS.FF'");

say "RowCacheSize: ", $dbh->{RowCacheSize};

my $data = gendata($count);
say Dumper($data) if $verbose;

my ($t0, $elapsed);

$dbh->do('truncate table speedtest') or die "SPEEDTEST truncate failed
+";
$t0 = [gettimeofday];
testins1($dbh, $data);
$elapsed = tv_interval($t0, [gettimeofday]);
say "1 Elapsed: $elapsed sec " . rowcnt($dbh) . " rows";

$dbh->do('truncate table speedtest') or die "SPEEDTEST truncate failed
+";
$t0 = [gettimeofday];
testins2($dbh, $data);
$elapsed = tv_interval($t0, [gettimeofday]);
say "2 Elapsed: $elapsed sec " . rowcnt($dbh) . " rows";

$dbh->do('truncate table speedtest') or die "SPEEDTEST truncate failed
+";
$t0 = [gettimeofday];
testins3($dbh, $data);
$elapsed = tv_interval($t0, [gettimeofday]);
say "3 Elapsed: $elapsed sec " . rowcnt($dbh) . " rows";

$dbh->do('truncate table speedtest') or die "SPEEDTEST truncate failed
+";
#$dbh->{ora_verbose} = 6;
#$dbh->{ora_oci_success_warn} = 1;
$t0 = [gettimeofday];
testins4($dbh, $data);
$elapsed = tv_interval($t0, [gettimeofday]);
say "4 Elapsed: $elapsed sec " . rowcnt($dbh) . " rows";

$dbh->disconnect;

exit 0;

__END__

[download]
C++ code 
//
// g++ -std=c++11 -Wall -g speedtest.cpp -I/usr/local/include
//      -I ${ORACLE_HOME}/rdbms/public -L ${ORACLE_HOME}/lib -lclntsh
//      -o speedtest && ./speedtest
//

#include <iostream>
#include <iomanip>
#include <vector>
#include <cstdio>
#include <cstdlib>
#include <ctime>

#define OTL_ORA12C
#define OTL_STL
#define OTL_EXCEPTION_STM_TEXT_SIZE 32767
#define OTL_ORA_UTF8

#if 0
#define STRINGIFY(x) #x
#define TOSTRING(x) STRINGIFY(x)

#define OTL_TRACE_LEVEL 0xff
#define OTL_TRACE_STREAM cerr
#define OTL_TRACE_LINE_PREFIX "MY OTL TRACE " __FILE__ ":" TOSTRING(__
+LINE__) " ==> "
#endif

using namespace std;

#include <otlv4.h>  // include the OTL 4.0 header file

class data {
    public:
        data(size_t nrows = 1)
            : c_numrows(nrows),
              c_strlen(8)
        {
            genint(id);
            genstr(name);
            gendate(aod);
            gencurr(curr1);
            gendouble(price1);
            gencurr(curr2);
            gendouble(price2);
        }

        void print(void);
        string pdate(struct tm);

        size_t c_numrows;

        vector<int>       id;
        vector<string>    name;
        vector<struct tm> aod;
        vector<string>    curr1;
        vector<double>    price1;
        vector<string>    curr2;
        vector<double>    price2;

    private:
        size_t c_strlen;

        void gencurr(vector<string> &);
        void genstr(vector<string> &);
        void genint(vector<int> &);
        void gendouble(vector<double> &);
        void gendate(vector<struct tm> &);

};

void data::print(void)
{
    cout << "Num of rows: " << c_numrows << '\n';
    for(size_t n=0; n<c_numrows; ++n) {
        cout << setw(8)  << n+1 << " " <<
                setw(10) << id[n]         << " - " <<
                            name[n]       << " - " <<
                            pdate(aod[n]) << " - " <<
                            curr1[n]      << " - " <<
                setw(10) << price1[n]     << " - " <<
                            curr2[n]      << " - " <<
                setw(10) << price2[n]     << '\n';
    }
    cout << "====================================" << endl;
}

void data::gencurr(vector<string> & cvec)
{
    for(size_t n=0; n<c_numrows; ++n) {
        string curr;
        for(size_t i=0; i<3; ++i) {
            char c = rand() % ('Z' - 'A') + 'A';
            curr.push_back(c);
        }
        cvec.push_back(curr);
    }
}

void data::genstr(vector<string> & vstr)
{
    for(size_t n=0; n<c_numrows; ++n) {
        string str;
        for(size_t i=0; i<c_strlen; ++i) {
            char s = rand() % ('Z' - 'A') + 'A';
            str.push_back(s);
        }
        vstr.push_back(str);
    }
}

void data::genint(vector<int> & vint)
{
    for(size_t n=0; n<c_numrows; ++n) {
        int i = rand() % 10000000 - 5000000;
        vint.push_back(i);
    }
}

void data::gendouble(vector<double> & vdouble)
{
    for(size_t n=0; n<c_numrows; ++n) {
        double d = (double)(rand() % 10000000 - 5000000) / 1000000.0;
        vdouble.push_back(d);
    }
}

void data::gendate(vector<struct tm> & vtm)
{
    for(size_t n=0; n<c_numrows; ++n) {
        struct tm *tm;
        time_t t = rand() % time(nullptr);
        tm = localtime(&t);
        vtm.push_back(*tm);
    }
}

string data::pdate(struct tm tm)
{
    char date[20];

    //int tm_sec;    /* Seconds (0-60) */
    //int tm_min;    /* Minutes (0-59) */
    //int tm_hour;   /* Hours (0-23) */
    //int tm_mday;   /* Day of the month (1-31) */
    //int tm_mon;    /* Month (0-11) */
    //int tm_year;   /* Year - 1900 */
    //int tm_wday;   /* Day of the week (0-6, Sunday = 0) */
    //int tm_yday;   /* Day in the year (0-365, 1 Jan = 0) */
    //int tm_isdst;  /* Daylight saving time */

    snprintf(date, 20, "%04d-%02d-%02d %02d:%02d:%02d",
        tm.tm_year+1900,
        tm.tm_mon+1,
        tm.tm_mday,
        tm.tm_hour,
        tm.tm_min,
        tm.tm_sec);

    string dstr(date);

    return dstr;
}

void insert(otl_connect &db, data &data)   // insert rows into table
{
    otl_stream o(
        5000,                                                       //
+ buffer size
            // Performance is controlled by a single parameter -- the 
+stream buffer size.
            // The buffer size is defined in logical rows to be insert
+ed into a table,
            // selected from a table / view in one round-trip to the d
+atabase
            // (a.k.a. batch size, array size).
        "insert into speedtest "
            "(id, name, aod, curr1, price1, curr2, price2) "
            "values "
            "(:id<int>, :name<char[32]>, :aod<char[20]>, :curr1<char[3
+2]>, :price1<double>, :curr2<char[32]>, :price2<double>)",
        db  // connect object
        );

    for(size_t i=0; i<data.c_numrows; ++i) {
        o << data.id[i]              <<
             data.name[i]            <<
             data.pdate(data.aod[i]) <<
             data.curr1[i]           <<
             data.price1[i]          <<
             data.curr2[i]           <<
             data.price2[i];
    }
}

int main(int argc, char *argv[])
{
    cout << "Progname: " << argv[0] << " " << ((argc >= 2 )? argv[1] :
+ "") << endl;

    //------------------------------------------------

    data d(argc >= 2 ? atoi(argv[1]) : 1);
    //d.print();

    //------------------------------------------------

    int i = argc >= 3 ? atoi(argv[2])-1 : 0;
    cout << setw(8) << i+1 << " " <<
        setw(10) << d.id[i]           << " - " <<
                    d.name[i]         << " - " <<
                    d.pdate(d.aod[i]) << " - " <<
                    d.curr1[i]        << " - " <<
        setw(10) << d.price1[i]       << " - " <<
                    d.curr2[i]        << " - " <<
        setw(10) << d.price2[i]       << endl;

    //------------------------------------------------

    otl_connect db; // connect object
    otl_connect::otl_initialize();

    // initialize OCI environment
    try {
        db.rlogon("???/???@???"); // connect to Oracle

        long rpc;
        rpc = otl_cursor::direct_exec(db, "alter session set nls_date_
+format = 'YYYY-MM-DD HH24:MI:SS'");
        cout << "Alter session : " << rpc << endl;
        rpc = otl_cursor::direct_exec(db, "truncate table speedtest");
        cout << "Speedtest truncated : " << rpc << endl;

        time_t t1 = time(NULL);
        insert(db, d);
        time_t t2 = time(NULL);
        cout << "Runtime: " << t2 - t1 << endl;

        db.commit();
    } catch(otl_exception& p) {    // intercept OTL exceptions
        cerr << p.msg      <<endl; // print out error message
        cerr << p.stm_text <<endl; // print out SQL that caused the er
+ror
        cerr << p.var_info <<endl; // print out the variable that caus
+ed the error
    } db.logoff(); // disconnect from Oracle

    return 0;
}

[download]

Replies are listed 'Best First'.
Re^3: DBD::Oracle insert row performance
by Marshall (Canon) on Jan 04, 2021 at 22:44 UTC
    There is a problem here: 
    my $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $passwd,
        {
            AutoCommit => 0,
            RaiseError => 1,
            RowCacheSize => 10000,
            ora_array_chunk_size => 100000,
            #ora_check_sql => 0,
            #ora_verbose => 0xff,
            #ora_oci_success_warn => 1,
        }
);

    [download]
    To my understanding, AutoCommit => 0; doesn't mean much. The default action will be to "commit" each insert operation no matter what this says, if you do not start a transaction explicitly.

    You need to start a transaction explicitly, insert huge amount of rows and then end the transaction explicitly. As I have suggested before, about 128K rows per commit will work fine.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom