Microsoft Access Programmers Fort Lauderdale Florida

Microsoft Access Programmer Information

Call MS Access Solutions at (323) 285-0939 For Complimentary Consultation

Optimizing the Performance of Your Queries

Optimizing your queries requires a great deal of practice and experimentation. Some queries involving a one-to-many relationship run more efficiently if you place the criteria on the "one" side of the relationship, for example. Others run more efficiently if you place the criteria on the "many" side. Understanding some basics can go a long way toward improving the performance of your queries and your application as a whole, as listed in the following:

1. Include as few columns in the resultset as possible. This limits the data returned to the client if you ever convert your application to a client/server environment.

2. Try to reduce the number of complex expressions contained in the query. Although including a complex expression in a query eliminates the need to build the expression into each form and report, the performance benefits gained sometimes are worth the trouble.

3. Use the Between operator rather than greater than (>) and less than (<) operators. The Access Database Engine is able to process Between more efficiently.

4. Group Totals queries by the field that is in the same table you are totaling. In other words, if you are totaling cost multiplied by price for each order in the Order Detail table, group by the order ID within the Order Detail table, not by the order ID within the Orders table. This reduces the number of rows that the Access Database Engine must process when running the query.

Now that you have seen what you can do with the design of your queries to improve performance, take a look at a couple of simple techniques you can use to improve the performance of your queries.

A simple but often neglected method of optimizing queries is to deliver your queries compiled. A query compiles when you open it in Datasheet view and then simply close it. If you modify a query and then save it, it is not compiled until the query runs. The Access Database Engine compiles all queries when you compact a database. Delivering precompiled queries ensures that they run as quickly as possible. It is therefore a good idea to compact a database before you distribute it to your users.

Finally, it is important that you compile your queries using the same amount of data that your application will contain. The reason is that the Access Database Engine's Query Optimizer optimizes the query differently, depending on the amount of data it finds. If you build a query using 100 records that will run on a live table containing 100,000 records, the query will not be optimized properly. You must rerun and resave your query using the correct quantity of data if you want the query to be optimized properly, or you must compact the database after the live data has been entered.

This material orginally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development. Reprinted here by author's permission.

When you need a Microsoft Access programmer for your Anaheim CA business, call MS Access Solutions at (323) 285-0939. We have over 25 years experience in Microsoft Access programmer solutions. We create Access database applications for all sectors, consisting of hospitals, government agencies, the U.S. military, universities, agriculture, workers services, and insurance provider. We can take care of the most advanced as well as complicated Access and also SQL Server database programming for your business as well as smaller projects, like fixing damaged Access database forms, MS Access reports, Access macros, and VBA code.

Access programmer cities we serve: Access Programmers Tampa Florida