A benchmark to compare DBI->do and DBI->prepare+execute

do_vs_prepare.pl

#!/usr/bin/perl
use strict;
use warnings FATAL => qw/all/;
sub MY () {__PACKAGE__}

use DBI;

sub usage {
  die <<END;
Usage: $0 [--do | --dbsub] DBFILE [N-RECORDS] [LEN-of-STR]
END
}

{
  my $self = bless {}, MY;

  my $testname;
  while (@ARGV and $ARGV[0] =~ /^--(\w+)/) {
    $testname = $1; shift;
  }
  $testname //= "do";

  my $testsub = $self->can("test_$testname")
    or die "No such test: $testname";

  @ARGV
    or usage();

  my ($dbname, $num, $len) = @ARGV;

  my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname"
			 , undef, undef
			 , {PrintError => 0, RaiseError => 1
			    , AutoCommit => 1});

  $dbh->do($self->sql_schema);

  {
    local $dbh->{AutoCommit};
    $testsub->($self, $dbh, $num // 100000, $len // 30);
  }
}

sub sql_schema {
  <<END;
create table if not exists memo(id integer primary key, subject text, body text)
END

}

sub sql_insert {
  <<END;
insert into memo(subject,body) values(?, ?)
END
}

sub input {
  my ($self, $num, $len) = @_;
  my ($subject, $body) = map {$_, $_} ("A" x $len);
}

sub test_do {
  my ($self, $dbh, $num, $len) = @_;
  my (@input) = $self->input($num, $len);
  my $sql = $self->sql_insert;
  for (my $i = 0; $i < $num; $i++) {
    $dbh->do($sql, undef, @input);
  }
}

sub test_dbsub {
  my ($self, $dbh, $num, $len) = @_;
  my (@input) = $self->input($num, $len);
  my $sub = do {
    my $sql = $self->sql_insert;
    my $sth = $dbh->prepare($sql);
    sub {
      $sth->execute(@_);
    };
  };

  for (my $i = 0; $i < $num; $i++) {
    $sub->(@input);
  }
}

__END__

=head1 BENCHMARK

  % time ./do_vs_prepare1.pl --do /tmp/test/test1.db
  ./do_vs_prepare1.pl --do /tmp/test/test1.db  2.04s user 0.02s system 99% cpu 2.062 total
  % time ./do_vs_prepare1.pl --do /tmp/test/test1.db
  ./do_vs_prepare1.pl --do /tmp/test/test1.db  2.16s user 0.01s system 99% cpu 2.174 total
  % time ./do_vs_prepare1.pl --do /tmp/test/test1.db
  ./do_vs_prepare1.pl --do /tmp/test/test1.db  2.02s user 0.02s system 99% cpu 2.040 total
  % time ./do_vs_prepare1.pl --dbsub /tmp/test/test2.db
  ./do_vs_prepare1.pl --dbsub /tmp/test/test2.db  0.19s user 0.01s system 99% cpu 0.202 total
  % time ./do_vs_prepare1.pl --dbsub /tmp/test/test2.db
  ./do_vs_prepare1.pl --dbsub /tmp/test/test2.db  0.20s user 0.01s system 99% cpu 0.204 total
  % time ./do_vs_prepare1.pl --dbsub /tmp/test/test2.db
  ./do_vs_prepare1.pl --dbsub /tmp/test/test2.db  0.22s user 0.01s system 99% cpu 0.231 total
  %

=cut