Performance V - rapid reports

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Avoid overlapping controls ■

It takes Access more time to render and draw controls that overlap each other than it does non-overlapping controls.

Think carefully about graphics sparingly ■

Use bitmap and other graphic objects sparingly (or not at all, if you ask me) as they can take more time to load and display than other controls. If you must use graphics, use the Image control instead of unbound object frames to display bitmaps. The Image Control is a faster and more efficient control type for graphic images. Also, if you have to use graphics use bitmaps with the lowest possible color depth. Consider black and white bitmaps where appropriate.

Access Help topic: Add a picture or object

Don't sort report queries ■

Don't base reports on queries with an ORDER BY clause. Reports use their Sorting and Grouping settings to sort and group, so the sort order of the underlying record set is ignored - it just slows the underlying query down.

Avoid expressions and functions ■

Try to avoid reports that sort or group on expressions or functions - this can be very sloooow.

Move expressions ■

Reports require at least one query execution per section. Because of this, your report may run faster if you take calculated fields (expressions) out of the underlying query and move it into the report.

Index appropriately ■■

Consider indexing any fields that are used for sorting/grouping or linking to subreports. Also conder indexing any suberport fields that are used for criteria. Of course, also bear in mind that remember that over-indexing can cause performance bottlenecks when editing, adding and deleting data.

Access Help topic: Indexed property

Minimize the report's dataset ■■

Base reports and subreports on queries instead of tables. By using a query, you can restrict the number of fields returned to the absolute minimum number, making data retrieval faster.

Avoid domain aggregate functions ■

Try to avoid using domain aggregate functions (e.g. DCount, DLookup) in a report's recordsource property. These are generally slow and will cause the report to open and display pages.

Use HasData property ■

Consider using the report's NoData event or HasData property to identify empty reports. You can then display a "sorry, no data" message and close the report.

Access Help topics: HasData property, NoData event

Avoid unnecessary property assignments ■

Only set properties that absolutely need to be set. Properties assignments can be relatively expensive in terms of performance, so make sure you're not setting any properties that don't need to be set.

Avoid the unnecessary ■

If a subreport is based on the same query as its parent report, or the query is similar, consider removing the sub report and placing its data in the main report. While not always feasible, this can speed up the overall report.

Simplify ■

Minimize the number of controls on your report. Loading controls is the biggest performance hit on report load times.