How to improve performance of SQL-SELECT statements in Visual FoxPro for Windows 3.0 and in Visual FoxPro for Windows 5.0 (136926)

MORE INFORMATION

Some things to consider:

• Joins slow down SELECT-SQL queries. If you don't really need a FROM table, remove it.
• If the query involves joins, make sure there are indexes that can be used to perform the join. That is, for "WHERE T1.F1 = T2.F2," make sure there are indexes on T1.F1 and/or T2.F2. (If you have indexes on both, the query optimizer will choose the one it thinks is better.)
• Sorts slow down SELECT-SQL queries. Sorts are performed to execute ORDER BY, GROUP BY, SELECT DISTINCT, and UNION DISTINCT.
  
  
  • Do not use SELECT DISTINCT unless you really need it. Note that if you have a GROUP BY, you don't also need a SELECT DISTINCT.
  • Do not use UNION DISTINCT unless you really need it. Note that if you don't specify ALL or DISTINCT after UNION, the default is DISTINCT, so you need to explicitly say UNION ALL.
  • Do not specify both GROUP BY and ORDER BY. If specify both and they are identical, the optimizer will automatically remove the ORDER BY.
• Specify only those fields you need in the SELECT-SQL clause. When the query engine creates temporary intermediate result tables, it has to include all the fields you specified in the SELECT-SQL clause, plus any that are referenced in the WHERE clause if they are needed for later execution. For complex queries, a field in the SELECT-SQL clause could be copied from one table to another several times. This can especially affect performance if the query includes memo fields.
• To take advantage of Rushmore optimization, ensure that filter conditions in the WHERE clause are Rushmore optimizable. You don't always need to make sure every condition is Rushmore optimizable, but make sure the Rushmore optimizable ones will greatly limit the rows from each source table.

For more information about the Rushmore technology and how to optimize your applications, please see the Visual FoxPro 3.0 Developer's Guide, chapter 17, "Optimizing Applications."