Microsoft KB Archive/156551

= How To Use SYS(3054) to Optimize a Query =

Article ID: 156551

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition

-



This article was previously published under Q156551



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 these steps:  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.  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   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   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.   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   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.   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 </li>  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. </li></ol>

Additional query words: query performance

Keywords: kbperformance kbfont kbhowto kbprint KB156551

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.