#!/usr/bin/perl

use strict;
use warnings;
use 5.14.0;

use Benchmark;
use DBI;

my $dbh = DBI->connect('dbi:SQLite::memory:', '','',
                       { 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") o
+r
                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], {

'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->do("BEGIN IMMEDIATE TRANSACTION");
     $dbh->do("DELETE FROM test_bulk_insert");
     $sth->execute_array({}, @newdata);
     $dbh->commit;
  } or die $@;

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

'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->do("BEGIN IMMEDIATE TRANSACTION");
    $dbh->do("DELETE FROM test_bulk_insert");
    $sth->execute(@$_) for @batch;
    $dbh->commit;
  } or die $@;
},

});

$dbh->disconnect;
[download]