Message-ID: <4CD06A81.1020800@vmsinfo.com>
Date: Tue, 02 Nov 2010 15:46:09 -0400
From: Mladen Gogala <mladen.gogala@vmsinfo.com>
Organization: VMS
User-Agent: Thunderbird 2.0.0.24 (X11/20101027)
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Array interface
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

-- 
 
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