DOCUMENT:Q136926  27-JUL-2001  [foxpro]
TITLE   :HOWTO: Improve Performance of SQL Queries
PRODUCT :Microsoft FoxPro
PROD/VER:
OPER/SYS:
KEYWORDS:kbSQL kbvfp300 kbvfp500 kbSQLProg

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a 
-------------------------------------------------------------------------------

SUMMARY
=======

This article provides tips to help you modify a SQL query to make it run faster.

MORE INFORMATION
================

Some things to consider:

 - Joins slow down 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 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 clause. When the query
   engine creates temporary intermediate result tables, it has to include all
   the fields you specified in the SELECT 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 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."

Additional query words: speed improve faster quicker tips

======================================================================
Keywords          : kbSQL kbvfp300 kbvfp500 kbSQLProg 
Technology        : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP500a
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.