[AccessD] Basic Query Design: Results

Jürgen Welz jwelz at hotmail.com
Mon Feb 13 15:54:07 CST 2006


All four methods returned exactly the same rows.

Of course the number of rows returned and variations in the number of 
criteria entered will probably favour one method over another.  I tried to 
take a realistic scenario where I have only 4 conditions and fewer than 20 
records are returned.  I have a suspicion that the last method I posted will 
return the best speed against larger datasets as it is based simply on join 
processing on single equality conditions for each additional condition and 
can use indexing every step of the way.  l suspect calculating a count may 
require all table rows to be returned.  Then again, returning consecutive 
subsets of data from the same table in order to process the same join might 
be slower than returning the table one time and running the processing on 
the count.

For typical data in our environment, it appears that using the count is the 
best approach.

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: Steve Erbach <erbachs at gmail.com>
>
>Jürgen,
>
>Now, the $64,000 question:  Did all the queries give you the same results?
>
>Just kidding!  I'm interested to see that the Count query is quickest.
>  It's certainly easier to understand...and should make for a lot less
>coding!
>
>Steve Erbach
>Neenah, WI
>http://TheTownCrank.blogspot.com





More information about the AccessD mailing list