| Microsoft Office 2000/Visual Basic Programmer's Guide | |
Most of the optimizations you perform for data access in a single-user environment create equal, if not greater, improvements in multiuser environments. When you optimize a single-user solution, you're concerned only with that solution's performance on a local machine. When you optimize a multiuser solution, reductions in network traffic help not only the individual user, but all users of the solution.
For example, consider the case in which your solution must search for a record in a table that has no indexes. In this scenario, the database engine must perform a sequential scan of the entire table to find a match. This means that the entire table must be sent from the server to the workstation's copy of the engine. If the table is large, this can be quite a lengthy operation. The problem is further compounded because the user running the search is waiting a long time for a response, and the high volume of network traffic is in turn slowing down other users. This situation can be alleviated by adding indexes to the table for the fields your solution searches on. However, keep in mind that having many indexes may actually cause increased network traffic and may reduce concurrency and efficient performance when many users are adding, deleting, or updating records because indexes must be updated to reflect these changes.
In another example, assume that your solution uses a set of tables containing lookup data that represents postal codes or lists of part numbers — data that doesn't change often. By moving this type of data to the local workstation, you can eliminate network access that may be made hundreds of times during the solution's typical execution.