Five Common Mistakes Affecting Access Database Optimisation and Performance
By Ben S Beitler
Building an Access database requires some time, effort and ideally a good level of
database knowledge though Microsoft Access provides a wealth of tools to assist and get
you up and running in minutes.
But some of the assist tools do not take on board and control some of the pitfalls most
developers tend to ignore and brush aside ultimately leading to a degrading performance
and dramatically a slowing of the database.
Optimisation is a methodology that must be seriously considered and applied more so for
the larger database file in order to control and increase performance.
Here are the five common mistakes users and developers need to be aware and handle accordingly:
1. Designing your tables adding fields; developers don't always consider the correct
data type and their field size which allocates memory whether used or not. For example, a
'Text' field size can be set between 1 and 255 characters which equates to roughly 1 byte
per character. If you have a reference number field which only requires 5 characters then
set this field size to 5 saving a maximum of 250 bytes per record. Multiply this by
several thousand records and you start to handle optimisation in your Access database.
2. I've seen tables which have over 100 fields in it with various data types and fields
sizes. The more fields in a table, the slower that table runs. It's not the number of
records that slows your database, it's the number of fields in a table. Keep the number of
fields to a minimum and set the best fit data type (and where applicable field size). If
you have a table that potentially has many fields (say more than 20) then split it into
two or more tables and have a join between them calling the additional tables when really required.
3. Queries can be really slow to run especially with multiple table joins (RDBMS) are
in use. The more tables joined with multiple criteria, sorting and using functions to
calculate, the slower the query will run and can take well over an hour to run in extreme
cases. Learning to index key fields (which is carried out in table designs) will
dramatically improve the speed of the query. Setting therefore primary and secondary keys
in tables is how you handle performance optimising your query.
4. Loading a form sometimes takes a while to display data and run general functionality
which is very noticeable for large volumes of data. To help handle performance consider
loading forms as unbound (with no data source) and use macros or VBA code to load filtered
recordsets instead. You can still use a bound data source but filter it first (via a
query) to help keep recordsets to a minimum optimising the form.
5. Running reports will result in many pages prior to printing and the preview and
layout views can take a while to load and display. Again, like forms, consider reducing
the recordset with a deeper query combined with key fields indexed. Additionally, having
sub-reports can cause performance problems too as each report has its own data source. Do
not have more than one sub-report; two at the most as it will degrade performance even more.
There are other reasons why your Access database will slow and other techniques to help
optimise that all important database. But the above five points will give you good head
start indeed!
Another tip for you! Once you have the bulk of your tables and keys set in your Access
database, run the 'Performance Analyzer' tool which will evaluate your database and report
back to you with either passive suggestions or critical warnings.
I invite you to keep up to date with my articles and eBooks which covers a lot of
details and can be found at
How to Build and Manage an Access Database
| 
About BTH
User Agreement
Privacy
Site Map
Contact
News Feeds
RSS Feed
Follow @Stephen Bucaro
Active Server Pages
