Hello all,
According to the documentation, it is more efficient to do (I'm paraphrasing):
$sth = $dbh->prepare($sql);
foreach (@ValueList) {
$sth->execute($_);
}
than
foreach (@ValueList) {
$dbh->do("Interpolated sql statement");
}
So I'm trying to figure out why, in the following example, Code1, which uses the prepare strategy, benchmarks slower than Code2, which uses repeated dos? BTW, in the actual program the @Result array is different for each element of the @Took array, hence the apparent circumlocution.
Thanks in advance,
Brian
#!/usr/bin/perl -w
use strict;
use Benchmark qw/:all/;
my $times = shift;
sub UpdateDBIar {
my ($arsql) = @_;
use DBI;
my $dbh = DBI->connect("dbi:mysql:database=SchoolToolsDev;host=loc
+alhost;user=stadmin;password=stadmin") or die "Couldn't connect to da
+tabase $DBI::errstr\n";
foreach my $sql (@$arsql) {
$dbh->do($sql) or die "Couldn't execute sql: $sql $dbh->errstr
+";
}
}
sub UpdateDBIar2 {
my ($arargs) = @_;
use DBI;
my $dbh = DBI->connect("dbi:mysql:database=SchoolToolsDev;host=loc
+alhost;user=stadmin;password=stadmin") or die "Couldn't connect to da
+tabase $DBI::errstr\n";
foreach my $argset (@$arargs) {
my $sql = $argset->[0];
my $artemp = $argset->[1];
my $sth = $dbh->prepare($sql);
foreach my $arg (@$artemp) {
$sth->execute(join ',', @$arg);
}
}
}
my $crCode1 = sub {
my @Result = (10734..10845);
my @Took = (qw/Baseline Q1 Q2 Q3 Q4/);
my $arargs;
foreach my $Took (@Took) {
my $sql = "UPDATE Tests SET $Took = '1' WHERE ID = ?";
my $artemp;
foreach (@Result) {
push @$artemp, [$_];
}
push @$arargs, [$sql, $artemp];
}
UpdateDBIar2($arargs);
};
my $crCode2 = sub {
my @Result = (10734..10845);
my @Took = (qw/Baseline Q1 Q2 Q3 Q4/);
my @sqls;
foreach my $Took (@Took) {
foreach (@Result) {
push @sqls, "UPDATE Tests SET $Took = '1' WHERE ID = '$_'"
+;
}
}
UpdateDBIar(\@sqls);
};
timethese ($times, {
Code1 => $crCode1,
Code2 => $crCode2,
}
);
