./execute_cmp.pl 100 1000 100
Benchmark: timing 100 iterations of SQLite_new, SQLite_old...
SQLite_new: 15 wallclock secs (15.27 usr +  0.07 sys = 15.34 CPU) @  6.52/s (n=100)
SQLite_old:  5 wallclock secs ( 5.91 usr +  0.03 sys =  5.94 CPU) @ 16.84/s (n=100)
Benchmark: timing 100 iterations of PostgreSQL_new, PostgreSQL_old...
PostgreSQL_new: 66 wallclock secs (26.55 usr +  1.75 sys = 28.30 CPU) @  3.53/s (n=100)
PostgreSQL_old: 37 wallclock secs ( 7.67 usr +  1.14 sys =  8.81 CPU) @ 11.35/s (n=100)
Benchmark: timing 100 iterations of MySQL_new, MySQL_old...
 MySQL_new: 58 wallclock secs (26.45 usr +  1.43 sys = 27.88 CPU) @  3.59/s (n=100)
 MySQL_old: 30 wallclock secs ( 8.09 usr +  0.85 sys =  8.94 CPU) @ 11.19/s (n=100)

##</code><code>##

#!/usr/bin/env perl

use strict;
use warnings;
use 5.14.0;

use Benchmark;
use DBI;

my @databases = (
    { name => 'SQLite',     dsn => 'dbi:SQLite::memory:' },
    { name => 'PostgreSQL', dsn => 'dbi:Pg:dbname=test_execute' },
    {   name => 'MySQL',
        dsn  => 'dbi:mysql:test_execute',
        user => 'test_execute',
        pass => 'password',
    },
);

for my $database (@databases) {

    my $dbh = DBI->connect(
        $database->{dsn},
        $database->{user},
        $database->{pass},
        {   AutoCommit => 1,
            RaiseError => 1,
            PrintError => 0,
            ChopBlanks => 1
        }
    ) or die DBI->errstr;

    $dbh->do("CREATE TABLE test_bulk_insert (\n"
            . (join ",\n", map {"field$_ int"} (1 .. $ARGV[0])) . ");\n")
        or die $dbh->errstr;

    my $sth
        = $dbh->prepare("INSERT INTO test_bulk_insert (\n"
            . (join ",\n", map {"field$_"} (1 .. $ARGV[0]))
            . ') VALUES ('
            . (join ",\n", map {'?'} (1 .. $ARGV[0])) . ')')
        or die $dbh->errstr;
    my @newdata;
    push @newdata, [] for (1 .. $ARGV[0]);
    $sth->bind_param_array($_ + 1, $newdata[$_]) for keys @newdata;

    my $data;
    $data .= sprintf(
        ("%-4d" x $ARGV[0]) . "\n",
        map { int rand 9999 } (1 .. $ARGV[0])
    ) for (1 .. $ARGV[1]);
    my $template = "A4" x $ARGV[0];

    timethese(
        $ARGV[2],
        {

            "$database->{name}_new" => sub {
                my $buf = $data;
                my ($curr, $prev);

                while ($buf =~ /\n/g) {
                    $prev = defined $curr ? $curr : 0;
                    $curr = pos $buf;
                    my $_ = substr $buf, $prev, $curr - $prev;
                    chomp;
                    my @cols = unpack $template;
                    push $_, shift @cols for (@newdata);
                }

                eval {
                    $dbh->begin_work();
                    $dbh->do("DELETE FROM test_bulk_insert");
                    $sth->execute_array({}, @newdata);
                    $dbh->commit;
                } or die $@;

                $#{ $newdata[$_] } = -1 for keys @newdata;
            },

            "$database->{name}_old" => sub {
                my $buf = $data;
                my ($curr, $prev, @batch);

                while ($buf =~ /\n/g) {
                    $prev = defined $curr ? $curr : 0;
                    $curr = pos $buf;
                    my $_ = substr $buf, $prev, $curr - $prev;
                    chomp;
                    push @batch, [unpack $template];
                }

                eval {
                    $dbh->begin_work();
                    $dbh->do("DELETE FROM test_bulk_insert");
                    $sth->execute(@$_) for @batch;
                    $dbh->commit;
                } or die $@;
            },

        }
    );

    $dbh->disconnect;
}