#!/usr/bin/perl -wT
# prepare-compare.pl

use strict;
use diagnostics;
use DBI;

use Time::HiRes;

my $dbh = DBI->connect('DBI:mysql:dbname=mdphd',
                       'bradford', '', {AutoCommit=>1})
  or die "Problem connecting: $DBI::errstr\n";

my @names;
# read list of names for which to search
while (<>) {
  chomp;
  # the '%' is a wildcard in SQL's LIKE clause
  push @names, "%$_%";
}

# first without preparation:
my $begin_unprepared = Time::HiRes::time;

foreach (@names) {
  my $quoted_name = $dbh->quote($_);
  my $sql = qq{
SELECT A.last_name, A.first_name, A.email, B.name, B.web_addr, C.name,
   A.homepage, A.acad_interests, A.other_interests, A.last_modified
FROM students_pub A, dept B, status C
WHERE login = $quoted_name AND B.code = A.department AND C.code = A.status
  };
  my $sth = $dbh->prepare($sql);
  $sth->execute() or die "Error!\nSQL:\t$sql\nError:\t$DBI::errstr\n";
  my $results = $sth->fetchall_arrayref or die "Problem fetching: $DBI::errstr\n";
  $sth->finish;
}

my $end_unprepared = Time::HiRes::time;

# now with preparation
my $begin_prepared = Time::HiRes::time;

my $sql = <<'EOSQL';
SELECT A.last_name, A.first_name, A.email, B.name, B.web_addr, C.name,
   A.homepage, A.acad_interests, A.other_interests, A.last_modified
FROM students_pub A, dept B, status C
WHERE login = ? AND B.code = A.department AND C.code = A.status
EOSQL
my $sth = $dbh->prepare($sql);
foreach (@names) {
  $sth->execute($_) or die "Error!\nSQL:\t$sql\nError:\t$DBI::errstr\n";
  my $results = $sth->fetchall_arrayref or die "Problem fetching: $DBI::errstr\n";
  $sth->finish;
}

my $end_prepared = Time::HiRes::time;

print "\nUnprepared:\t$begin_unprepared-$end_unprepared\t",
  $end_unprepared-$begin_unprepared;
print "\nPrepared:\t$begin_prepared-$end_prepared\t",
  $end_prepared-$begin_prepared;
print "\nNumber of names:\t", scalar(@names), "\n";

$dbh->disconnect;

syntax highlighted by Code2HTML, v. 0.8.11