Optimizing Queries

3 4

Queries are a fundamental database tool, and as such, you'll want them to be as efficient as possible. Here are a few guidelines for producing high-performance queries:

  • Avoid using the asterisk ( *) method. A query runs faster if you list all the field names in the design grid.
  • Limit fields. Include only the fields you really need. If you must include a field (say, for setting a criterion) but you don't need to display the field, clear its Show check box in the design grid.
  • Avoid nested queries. A nested query is a query based on another query. Most of the time, you can write a nested query as a subquery. Copy the nested query's Jet SQL statement from the SQL window to the Clipboard. Then open the original query in the SQL window, and replace the query reference with the nested query's Jet SQL statement. Be sure to enclose the nested query's statement in parentheses. You might need to tweak things a bit, but most of the time this shortcut works well.
  • Avoid multiple SQL GROUP BY clauses. A single GROUP BY clause doesn't affect a query's performance, but several GROUP BY clauses in the same query can slow performance.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net