Hi.
I'm currently learning DBI, and have a question regarding the
quote method. I'm accessing a MySQL database on my local machine. I will
eventually rewrite some of my CGI scripts using what I learn from DBI.
Basically, I have a list of names in an array, and I would like to
search a database for rows matching any of the names. I ran benchmarks on
a few different procedures to accomplish this, and I found that the
fastest is to put all the names in one huge SQL statement, like such:
my @want = qw/name1 name2 name3/; #etc ...
sub by_big_where {
# This is 'where' when Benchmark is running.
my $string = "'" . join ("', '" , @want) . "'";
my $sth = $dbh->prepare("select * from tablename
where name in ( $string )");
$sth->execute();
my @row;
while (@row = $sth->fetchrow_array()) {
#print "Row is: @row\n";
}
}
***Note: In practice, the @want array would be populated by the CGI.pm
"param" function, possibly called on a checkbox group, and an element of
the array may contain spaces. Both subroutines listed here work fine in
that case.
This works as expected. Two other methods work as well, but are
slower, according to Benchmark. (Using foreach to loop through @want, and
writing an $sth for each value, as derived from the example on page 123 of
the cheetah book, which was the slowest, and using bind values as
explained in that same section, which was faster than the "foreach"
method, but still slower than the method above.) This makes sense, since
the other two methods do an execute for each element in @want, but the
subroutine above only does one execute, regardless of the size of @want.
I realize things may get messed up trying to do quoting by hand,
so I would like to use the quote method, if possible. The only way I
could think of to do this that actually works is the following:
my @want = qw/name1 name2 name3/; #etc ...
sub by_big_where2 {
# This is 'where2' when benchmark is running.
my @qlist = map { $dbh->quote($_) } @want;
my $qstring = join(', ', @qlist);
my $sth = $dbh->prepare("select * from tablename
where name in ( $qstring )");
$sth->execute();
my @row;
while (@row = $sth->fetchrow_array()) {
# print "Row is: @row\n";
}
}
This works just fine, but it's a little bit slower than the first
subroutine, which is understandable, as this one calls the quote method
for each element in the @want array.
Benchmark: timing 10000 iterations of where, where2...
where: 16 wallclock secs ( 8.63 usr + 0.86 sys = 9.49 CPU) @
1053.74/s (n=10000)
where2: 17 wallclock secs ( 8.38 usr + 0.97 sys = 9.35 CPU) @
1069.52/s (n=10000)
Basically, are there any other ways I can get a list of values
into and SQL statement like "...WHERE name IN ( 'name1', 'name2' ... )"?
Maybe something that would only call the quote method only once? The
speed difference between the two methods is negligible. Should I just use
the safer "quote" method at the expense of a little bit of speed?
I would appreciate any tips with this. Thanks in advance.
Jamie Kufrovich
Egg, eggie (at) sunlink (dot) net
FMSp3a/MS3a A- C D H+ M+ P+++ R+ T W Z+
Sp++/p# RLCT a+ cl++ d? e++ f h* i+ j p+ sm+
