Rick,
I am not convinced that this statement applies only to queries. Consider
the following statement, which comes from FMS's Total Access Analyzer on a
Performance report, for an analysis of the the Orders Subform in the
Northwind sample database. The Private Sub ProductID_AfterUpdate procedure
includes the use of DLookup:
Module Domain Functions in Code
Domain functions such as DLookup, DMin, Max, etc. are often slower than an
equivalent saved query. Where possible, consider replacing Domain functions
with saved queries that are optimized for performance.
Module (Form): Orders Subform
Form [Orders Subform] contains one or more uses of these Domain functions:
DLookup
Granted, the FMS report does not give a reason why domain aggregrate
functions are often slower. I strongly suspect that it is because "When you
use a domain aggregate function, Access retrieves all of the data in the
(linked) table to execute the query."
Tom
___________________________________
:
That article specifically says "In Queries" (a pretty big omission). I don't
believe that in a single domain aggregate used on a form or report that the
"entire table" is being pulled, at least not if the field with criteria has
an
index on it. Testing just doesn't produce slow enough results for that to be
the case.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
___________________________________
:
Hi Richard,
Domain aggregrate functions are like candy; they may taste good, but they
may not be so good for you. If your data is stored on your local hard drive,
then you can get away with using these functions, usually without too much
problem. However, if your data is stored on a file server (ie. split
multi-user DB, with the shared data on a file server in a JET database), then
domain aggregrate functions can be a problem.
According to this article:
http://office.microsoft.com/en-us/assistance/HP051874531033.aspx
(see the 4th bullet of the 4th link titled "Improve performance of linked
tables")
"When you use a domain aggregate function, Access retrieves all of the data
in the linked table to execute the query."
I take this to mean that Access would need to pull all of the records over
the wire, in order to use a domain aggregrate function. If you make sure to
index any fields that are used in the criteria or sorting of queries, you
have a much better chance of avoiding table scans. Thus, my vote would be to
open a recordset and do a recordcount, or perhaps just run a properly indexed
query to return the count, ie.
SELECT Count(*) AS CustTotal FROM Customers;
Again, if you apply any criteria (ie. WHERE clause) to this type of query,
make sure to index the fields involved.
Tom
___________________________________
:
Hi
I am using a Dcount function to count the number or records in a saved
query.
How do I change the recordsource of the query at runtime and do a Dcount?
Or should I open a recordset and do a recordcount instead?
Which way would be more efficient?
Many thanks in advance.
Richard
