Lookup Functions vs Subqueries - Performance Difference?

[Guest]

I've got a query in MS Access 2000 that's got several fields with lookup
functions, such as this:

Prior Date: DMax("AS_OF_DT","DAILY_LOG","AS_OF_DT<#" & [Current Date] & "#")

From a design standpoint, it was easier for me to create the query using
something like that. However, the query has grown (more fields, more data)
and it's starting to run very slow. I know I could probably re-write the
above expression as a sub-query, something like:

Prior Date: ("SELECT MAX(AS_OF_DT) FROM DAILY_LOG WHERE AS_OF_DT<>#" &
[Current Date] & "#")

However, before I go to the time to do that, can anyone tell me whether I
would see an appreciable increase in query performance?

 

[Allen Browne]

In general, a subquery will be much faster than a domain aggregate function.

We need to qualify that though:

1. If the subquery returns a field, the query results will be read-only
result, so is not suitable for forms you need to edit.

2. A subqeury in the SELECT clause can trigger an error when you use the
query in a report that has any sorting and grouping. The report complains
about a "Multi-level group-by", since the report itself needs to group the
results. Creating another query into the first one ("SELECT * FROM Query1
and using that as the source for the report sometimes works around this
issue.

3. Since you cannot specify the query plan JET should use, the actual
performance of a particular subquery can vary from lightning to snail
depending on how JET compiles it. Sometimes you just have to fiddle. This
also means it's important to use real sized data samples for testing.

4. When using subqueries, it is *very* easy to crash Access ("shut down by
Windows".) There are some really major flaws in how Microsoft has
implemented them. I don't have all the specifics, but examples include:
~ outer joins in stacked queries based on the results of a subquery in the
lower level query (which you would expect to be efficient), and
~ outer joins on tables that have yes/no fields where you use criteria on
the result (this issue seems to relate to JET's inability to handle Nulls in
a yes/no field.)

Where the subquery returns a field, don't forget to ensure uniqueness.
Adding the primary key in the ORDER BY clause is often a simple solution.

Hope that helps.

 

[Guest]

DMAX is evaluated once for each line of the query. It is
relatively slow, but it is a simple query.

MAX is evaluated once for the subquery. It can be quite
complex when it is part of a larger query.

The effect is that you can normally view the first line of
a query with a DMAX much faster than you can view
the first line of a query with MAX, but,

you can normally view the last line of the query with MAX
much faster than you can view the last line of the query with
DMAX.

As always, it may be different for a specific query and data.

(david)