On Sun, 8 Jul 2001, M.W. Koskamp wrote:
[snippage]
> You could also issue a:
> select count(keyfield)
> from sometable
> where someclause
>
> I did some benchmarks and found that is was faster then a where exists.
Hi. The original poster, here. I took four of the suggestions
that folks gave (attached below) and benchmarked them four times; using a
value known to exist in the table and one known not to exist, for both
large and small tables, and for the most part, the results seemed to vary,
depending on those two factors. Three things of note:
--The "unbare count" method (select count(number) from...)
consitently came in either 3rd or 4th place, in terms of speed. 'number'
is the name of the PK column being searched.
--Using the "select" method (select number from...; again,
'number' is the name of the PK column) came in either 1st or 2nd except
for one case: checking a small table, and if the value does exist in the
table. It came in last place in this one case.
--The "exists" method (select 1 from...) came in fourth with a
large table and if the value does exist, otherwise it came in first or
second.
Lastly, the "bare count" method (select count(*) from...) varied
from first to last, speed-wise, depending, in no discernable pattern to
me.
The largest difference in speed among the various methods was
around 15% (in one case only, I think; the other three were less than
10%), which isn't really all that much for my use (it might be big
for someone else, though). I guess I'll just compare the "exists" and
"select" methods more closely.
I'll use this for a guestbook script that I'm writing, mainly as a
learning exercise, so the database will start out small, then most likely
grow.
I don't really know what to make of the differences. Any ideas?
Jamie
---------------------------------------------
### Just the subroutines are shown below. The definition of $number,
### the database connections, and other trivial things are omitted.
sub bare_count {
my $value = $dbh->selectrow_array
("select count(*) from courses where number = '$number'");
}
sub unbare_count {
my $value = $dbh->selectrow_array
("select count(number) from courses where number = '$number'");
}
sub exists {
my $value = $dbh->selectrow_array
("select 1 from courses where number = '$number'");
}
sub select {
my $value = $dbh->selectrow_array
("select number from courses where number = '$number'");
}
