• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 17. Optimizing Your Application > Optimizing the Performance of Your Qu...

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.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint