On Dec 31 2007, 6:10*am, lyle <lyle.fairfi...@gmail.comwrote:
Quote:
>
From a Microsoft article of many years ago:
>
"Use Count(*) instead of Count([ColumnName]). Jet has built-in
optimizations that make Count(*) much faster than column-based
counts."
>
As I understand it, at that time Jet counted in any available index
for Count(*), but it scanned the table for Count([ColumnName]).
>
Is this still pertinent for ACE, T-SQL and JET 4.0? I'm not sure, but
my guess is "Yes".
From Jet Database Engine Programmer's Guide (Jet 3.5), Second Edition,
ISBN 1-57231-342-0, p. 159 (Great book Lyle; thanks for bringing it to
my attention.):
Common Pitfalls
Using Count(<fieldname>) instead of Count(*). When you have to
determine the number of records, you should use Count(*) rather than
Count(<fieldname>) because there are Rushmore optimizaitons that allow
Count(*) to be executed much more quickly than Count(<fieldname>).
Since the argument is that the benefit is due to Rushmore Technology
on the indexes, my guess is "yes" also. By performing the
intersection or union operations on the indexes rather than on the
data, Rushmore Technology benefits greatly from multiple indexes used
in JOIN's, used for criteria or used for sorting -- to a point.
op cit:
This makes indexing many fields advantageous. However, keep in mind
that adding indexes to fields can cause performance to suffer.
I assume they're referring to the performance problems presented in
the A97 Help File (Optimize general table performance):
Indexes aren't appropriate in all cases, however. Indexes add to the
size of the .mdb file, reduce concurrency (the ability of more than
one user to modify a page at the same time) in multiuser applications,
and decrease performance when you update data in fields that are
indexed, or when you add or delete records. It's a good idea to
experiment to determine which fields should be indexed. Adding an
index may speed up a query one second, but slow down adding a row of
data by two seconds and cause locking problems. Or it may add
negligible gains depending on which other fields are indexed. For
example, adding an index to a PostalCode field may provide very little
performance gain if a CompanyName field and LastName field in the
table are already indexed. Regardless of the types of queries you
create, you should only index fields that have mostly unique values.
Hmm..., maybe by using less indexes I can use more bound forms since I
won't get as much locking :-). I don't think I'll even try that!
James A. Fortune
CDMAPoster@FortuneJames.com 
