Array interface

I wrote a little Perl script, intended to test the difference that array
insert makes with PostgreSQL. Imagine my surprise when a single  record
insert into a local database was faster than batches of 100 records.
Here are the two respective routines:

sub do_ssql
{
    my $exec_cnt = 0;
    while (<FL>)
    {
        chomp;
        my @row = split /$sep/;
        $sth->execute(@row);
        $exec_cnt++;
    }
    $dbh->commit();
    print "Insert executed $exec_cnt times.\n";
}

sub do_msql
{
    my $bsz = shift;
    die("Batch size must be >0!\n") unless $bsz > 0;
    my $exec_cnt = 0;
    my @tstat;
    my (@col1, @col2, @col3);
    while (<FL>)
    {
        chomp;
        my @row = split /$sep/;
        push @col1, $row[0];
        push @col2, $row[1];
        push @col3, $row[2];
        if ($. % $bsz == 0)
        {
            my $tuples = $sth->execute_array({ArrayTupleStatus => \@tstat},
                                             \@col1, \@col2, \@col3);
            die("Multiple insert failed!\n") if (!$tuples);
            @col1 = ();
            @col2 = ();
            @col3 = ();
            $exec_cnt++;
        }

    }
    if ($#col1 >= 0)
    {
        my $tuples = $sth->execute_array({ArrayTupleStatus => \@tstat},
                                         \@col1, \@col2, \@col3);
        die("Multiple insert failed!\n") if (!$tuples);
        $exec_cnt++;
    }
    $dbh->commit();
    print "Insert executed $exec_cnt times.\n";
}


The variable "$sth" is a prepared  statement handle for the insert statement.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions