Network Considerations

So far, we have concentrated on writing code that fits into as small an amount of memory as possible, and can be executed as quickly as possible - or at least appears to do so. But that is only part of the story. One of the major reasons why Access database applications run slowly has nothing to do with memory footprints or code execution speed. Instead, the albatross around the neck of many applications is the vast amount of data that needs to be read from disk and passed across a network.

A network has two major drawbacks. Firstly, performance across a network - particularly a slow one - can be worse than performance against local tables. Secondly, you might find that your application generates a lot of network traffic. This will not make you popular with other users of the network, who find that their applications have slowed down considerably because of the log-jam. If either of these causes you a problem, consider:

• Searching on indexed fields in attached tables

• Putting non-table objects on the local machine

• Disabling AutoExpand

Search on Indexed Fields in Attached Tables

One method of minimizing the amount of data that an application has to read from disk is to ensure that fields used in the queries are indexed. If you run a query with a criterion against a field that is indexed, Access will be able to determine the exact data pages which contain the records it needs to retrieve in order to run the query. It will retrieve only the data pages that contain those records.

However, if you are running a query that uses a criterion against a field which is not indexed, Access will need to read into memory every single record in the underlying table to see whether it meets the criterion. If the table is large and is on a network server, this will result in large amounts of network traffic, and a very slow and frustrating query.

To look at an example of this in a little more detail, consider the following query:

 SELECT * FROM tblPerformance WHERE ID=4000 

If the ID field in tblPerformance is indexed, then the Jet engine knows that it will only need to retrieve the pages containing records with an ID of 4000 from the tblPerformance table.

However, if the ID field is not indexed, then Jet will have to read the whole of the tblPerformance table from disk and transfer it into the memory of the PC running the query. If the tblPerformance table is on a network server, this means that the whole table will need to be transferred across the network. Once it is in local memory, Jet can determine which records match the criterion by going through each record in turn and checking whether the ID field is equal to 4000 . This is known as a full table scan and is slow.

Imagine if the tblPerformance table contained half a million records, each approximately 260 bytes long. A table scan on an attached table would mean reading (500,000 x 260 bytes =) 130MB across the network and trying to fit them into local memory...

As a rule, therefore, always use indexes on fields which are involved in joins or which have criteria applied to them in queries.

Put Non-Table Objects on the Local Machine

Another way to minimize the amount of network traffic that a networked application generates is to place tables on the network server, but to place other objects into a local copy of the database. Then all users can share the data in the tables, but all other objects (queries, forms, reports , macros, and modules) will reside on the local computer. Consequently, when that object is activated - say, when a form is opened - the computer only needs to read it into memory from its local disk. This will generally be quicker than loading it over the network and will also mean a noticeable reduction in network traffic.

The downside of this strategy is that you will have to distribute a new copy of the database to each user whenever you revise the code or any of the objects in it. Despite this, most applications benefit from this sort of segmentation.

Disable AutoExpand

The AutoExpand property of a combo box forces Access to fill it automatically with a value that matches the text you have typed. Although this is a neat feature and can make the process of filling in forms less of a chore, it comes at a price - the table that supplies the values has to be queried as the user types in text. If the table on which the combo box is based resides across the network, the result may be a substantial increase in network traffic, especially if the combo box contains many values.