لطفا منتظر باشید ...
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:
- 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.
- 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.
- Use the Between operator rather than greater than (>) and less than (<) operators. The Jet Engine is able to process Between more efficiently.
- Use Count(*) rather than Count([column]). The Jet Engine is able to process Count more efficiently.
- 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 Jet 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. Jet 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. This is because Jet'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 compact the database after the live data has been entered.
