Everyone wants the performance of their database to be optimal. In particular, there is often a requirement for a specific query or object that is query based, to run faster.

The performance of a query is affected by the tables or queries that underly the query and by the complexity of the query. Table based forms are faster than query based forms and attached tables are slower than integral tables. Sometimes it may be wise to import rather than to attach frequently accessed foreign tables.

Access uses Rushmore techniques borrowed from FoxPro, to automatically optimise queries that contain two or more indexes. (Rushmore supports some, but not all foreign formats of attached table. For example, it supports FoxPro but not Btrieve. As Rushmore is an automatic process there is no need to understand it except to remember to index two or more query fields for all but the simplest of queries.)

Here are some of the methods that Advent IT use to optimise the speed of Access queries:

• Display the minimum number of fields in a query. Set criteria dependant fields that are not required in the dynaset to "not shown".
• Index all restriction based fields, all fields included in expressions, all sorted fields and all join fields.
• Use primary keys or unique indexes wherever possible.
• Use numeric rather than text primary keys.
• Use non blank unique fields.
• Avoid the use of IIf() function in queries.
• Avoid domain aggregate functions such as Dlookup().
• Make careful use of Between and Equal to, rather than > or < speeds up queries.
• Use fixed column headings in Crosstab queries.
• For reports based on queries use Portrait view in preference to Landscape and select Fast Laser Printing to Yes (View,Options,Other Properties).
• Use Make table queries for running reports on static data. These are called snapshot reports.
• Use Count (*) rather than Count(Column).
• When creating restrictions on a joined column in one-to-many relationships, test out the comparative performance when placing the restriction on the "one" side or the "many" side. The "one" side is not always the fastest — the "many" may have markedly fewer records.
• Short table and field names run faster than long names.
• Normalise tables — join strategies execute more quickly on smaller tables.
• Denormalise tables — reduce the number of joins. Get the balance right between normalisation and denormalisation by experiment.
• Avoid the use of Distinct Row queries — Union queries do not need the distinct row feature as they are automatically return unique fields unless set to Union All.

Comments are closed.