Chapter 24: Optimizing Access Applications
859
l (^) Limit the columns of data returned in a select query to only those you need. If you don’t
need the information from a field, don’t return it in the query. Queries run much faster
when returning less information.
Tip
If you need to use a field for a query condition and it isn’t necessary to display the field in the results table,
deselect the View check box to suppress displaying the field and its contents.
l When you need to return a count of the records returned by an SQL statement, use
Count() instead of Count([FieldName]) because Count() is considerably faster.
Count(*) counts records that contain null fields; Count([FieldName]) checks for
nulls and disqualifies them from the count. This means that Count doesn’t count records
that have a null in the specified field.
Tip
You may also replace FieldName with an expression in the Count function, but this slows down the function
even further.
l (^) Avoid using calculated fields in nested queries. A calculated field in a subordinate query
considerably slows down the top-level query. You should use calculated fields only in top-
level queries, and even then, only when necessary.
l When you need to group records by the values of a field used in a join, specify the Group
By for the field that is in the same table that you’re totaling. You can drag the joined field
from either table, but using Group By on the field from the table that you’re totaling
yields faster results.
l Domain aggregate functions (such as DLookup or DCount) that are used as expressions
considerably slow down queries. Instead, you should add the table to the query or use a
subquery to return the needed information.
l (^) As with VBA code modules, queries are compiled. To compile a query, the query opti-
mizer evaluates the query to determine the fastest way to execute the query. If a query is
saved in a compiled state, it runs at its fastest speed the first time that you execute it. If it
isn’t compiled, it takes longer the first time because it must be compiled, but then it runs
faster in succeeding executions. To compile a query, run the query by opening it in
Datasheet view and then close the query without saving it. If you make changes to the
query definition, run the query again after saving your changes, and then close it without
saving it.
l (^) If you really want to squeeze the most out of your queries, experiment by creating your
queries in different ways (such as specifying different types of joins). You’ll be surprised at
the varying results.
Getting the most from your forms and reports
Forms and reports can slow an application by taking a long time to load or process information.
You can perform a number of tasks to increase the performance of forms and reports.