gistfile1.pl

use strict; use warnings; use 5.010;
use DBI;
use Benchmark qw{:all} ;  

my $EXECUTES_PER_PREPARE = 10;
my $BENCH_TIMES = int(200_000 / $EXECUTES_PER_PREPARE);

my $dbh = DBI->connect('DBI:mysql:database=test', '', '',
                       {RaiseError => 1, AutoCommit => 0}
                      );

sub prepareTable {
    my $dropTable   = q|DROP TABLE IF EXISTS test.table|;
    $dbh->do( $dropTable );

    my $createTable = q{
    CREATE TABLE test.table (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `value` varchar(60),
      PRIMARY KEY (`id`)
    )
    };
    $dbh->do( $createTable );
}


my $sql = q|INSERT INTO test.table ( value ) VALUES ( ? ) |;
my %benchmarks = (
    'my'    => sub {
        my $sth = $dbh->prepare($sql);
        for my $val (1..$EXECUTES_PER_PREPARE) {
            $sth->execute($val);
        }
    },
    'state' => sub {
        state $sth = $dbh->prepare($sql);
        for my $val (1..$EXECUTES_PER_PREPARE) {
            $sth->execute($val);
        }
    },
    'prepare_cached' => sub {
        my $sth = $dbh->prepare_cached($sql);
        for my $val (1..$EXECUTES_PER_PREPARE) {
            $sth->execute($val);
        }
    },

);  

my %times;
prepareTable;
for my $name (keys %benchmarks) {
    my $code = $benchmarks{$name};
    $times{$name} = timeit( $BENCH_TIMES, $code );
    $dbh->rollback;
}

cmpthese(\%times);