RunSql vs Open Query
- Thread starter dan-cat
- Start date
Fornatian
Dim Person
- Local time
- Today, 14:37
- Joined
- Sep 1, 2000
- Messages
- 1,396
Query Optimization Tips...
1. Display the minimum number of fields in a query. Set criteria dependant fields that are not required in the dynast to "not shown".
2. Index all restriction based fields, all fields included in expressions, all sorted fields and all join fields.
3. Use primary keys or unique indexes wherever possible.
4. Use numeric rather than text primary keys.
5. Use non-blank unique fields.
6. Avoid the use of If() function in queries.
7. Avoid domain aggregate functions such as Dlookup().
8. Make careful use of Between and Equal to, rather than > or < speeds up queries.
9. Use fixed column headings in Crosstab queries.
10. For reports based on queries use Portrait view in preference to Landscape and select Fast Laser Printing to Yes (View,Options,Other Properties).
11. Use Make table queries for running reports on static data. These are called snapshot reports.
12. Use Count (*) rather than Count(Column).
13. When creating restrictions on a joined column in one-to-many relationships, test out the comparative performance when placing the restriction on the "one" side or the "many" side. The "one" side is not always the fastest - the "many" may have markedly fewer records.
14. Short table and field names run faster than long names.
15. Normalize tables - join strategies execute more quickly on smaller tables.
16. Denormalize tables - reduce the number of joins. Get the balance right between normalization and denormalization by experiment.
17. Avoid the use of Distinct Row queries - Union queries do not need the distinct row feature as they are automatically return unique fields unless set to Union All.
1. Display the minimum number of fields in a query. Set criteria dependant fields that are not required in the dynast to "not shown".
2. Index all restriction based fields, all fields included in expressions, all sorted fields and all join fields.
3. Use primary keys or unique indexes wherever possible.
4. Use numeric rather than text primary keys.
5. Use non-blank unique fields.
6. Avoid the use of If() function in queries.
7. Avoid domain aggregate functions such as Dlookup().
8. Make careful use of Between and Equal to, rather than > or < speeds up queries.
9. Use fixed column headings in Crosstab queries.
10. For reports based on queries use Portrait view in preference to Landscape and select Fast Laser Printing to Yes (View,Options,Other Properties).
11. Use Make table queries for running reports on static data. These are called snapshot reports.
12. Use Count (*) rather than Count(Column).
13. When creating restrictions on a joined column in one-to-many relationships, test out the comparative performance when placing the restriction on the "one" side or the "many" side. The "one" side is not always the fastest - the "many" may have markedly fewer records.
14. Short table and field names run faster than long names.
15. Normalize tables - join strategies execute more quickly on smaller tables.
16. Denormalize tables - reduce the number of joins. Get the balance right between normalization and denormalization by experiment.
17. Avoid the use of Distinct Row queries - Union queries do not need the distinct row feature as they are automatically return unique fields unless set to Union All.
