DOCUMENT:Q156551  11-SEP-2001  [foxpro]
TITLE   :HOWTO: Use SYS(3054) to Optimize a Query
PRODUCT :Microsoft FoxPro
PROD/VER:
OPER/SYS:
KEYWORDS:kbprint kbFont kbPrinting kbvfp500 kbvfp600

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

 - Microsoft Visual FoxPro for Windows, versions 5.0, 6.0 
-------------------------------------------------------------------------------

SUMMARY
=======

Visual FoxPro 5.0 added a new SYS() function that allows you to see the Rushmore
optimization level of query. This information can help you optimize your queries
in relation to their use of Rushmore.

SYS(3054) actually accepts the following three different parameters:

 - 1 - to turn on reporting of filter optimization

 - 11 - to turn on reporting of join optimization

 - 0 - to turn off the reporting of optimization levels

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

To demonstrate how this function works, follow the steps below:

1. Copy the Customer.dbf and Orders.dbf files from the \Samples\Data folder to
   another folder where you can make changes to the tables. For the purpose of
   this example, copy only the .dbf files as they are the only files that you
   need. After you have copied the files, open them exclusively so you can
   remove the database container references and the Structural CDXs. When you
   open the tables, a dialog box opens stating that the Testdata.dbc could not
   be opened, click delete (you do not need the .dbc for this example). If you
   have SET SAFETY ON, you also get a dialog box stating that the Structural CDX
   file could not be found, click ignore.

2. Now you can type the following in the Command window:

         ? SYS(3054,1)
         SELECT * FROM customer WHERE customer.country = 'USA'

   The following is returned:

         Rushmore optimization level for table customer: none

3. Next, add an index tag on country field and repeat the query above:

         INDEX on country TAG country

   NOTE: Be sure to re-select the customer table first (SELECT customer) so you
   are operating on the original data. Now, you should see the following:

         Using index tag Country to rushmore optimize table customer
         Rushmore optimization level for table customer: full

4. Now, if you try the following query:

         SELECT * FROM customer WHERE customer.country = 'USA' and ;
            customer.maxordamt>2000

   You see the following:

         Using index tag Country to rushmore optimize table customer
         Rushmore optimization level for table customer: partial

   Adding a tag on maxordamt makes the second query fully optimizable.

5. Next, if you join the two tables:

         SELECT * FROM customer, orders WHERE ;
            customer.cust_id = orders.cust_id

   You see the following:

         Rushmore optimization level for table customer: none
         Rushmore optimization level for table orders: none

6. Now, add tags to both tables for the cust_id field:

         SELECT customer
         INDEX ON cust_id TAG cust_id
         SELECT orders
         INDEX ON cust_id TAG cust_id

   Try the query again:

         SELECT * FROM customer, orders WHERE ;
            customer.cust_id = orders.cust_id

   You see the following:

         Rushmore optimization level for table customer: none
         Rushmore optimization level for table orders: none

   Since the query is joining the tables and not doing any filtering, this is
   correct. Since there is no filter, there is no filter optimization.

7. Next, issue the following command:

         ? SYS(3054,11)

   Try the query again and you see the following:

         Rushmore optimization level for table customer: none
         Rushmore optimization level for table orders: none
         Joining table customer and table orders using index tag Cust_id

8. Now, try this query:

      SELECT * FROM customer, orders WHERE customer.cust_id=orders.cust_id ;
         AND customer.country = 'USA' AND orders.order_amt>500

   You see the following:

         Rushmore optimization level for table customer: full
         Rushmore optimization level for table orders: none
         Joining table customer and table orders using index tag Cust_id

   Since there is no tag for orders.order_amt, that filter expression cannot be
   optimized. Add a tag to orders for the order_amt field and the optimization
   for orders is full.

Additional query words: query performance

======================================================================
Keywords          : kbprint kbFont kbPrinting kbvfp500 kbvfp600 
Technology        : kbVFPsearch kbAudDeveloper kbVFP500 kbVFP600
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.