Optimizing Filters and Joins

To make optimization decisions for a view or query, you might need to know the execution plan: the order that joins and filter clauses will be evaluated. Using the SYS(3054) function, you can display one of three Rushmore Query Optimization levels. The three levels indicate the degree to which the filter conditions or join conditions were able to use Rushmore optimization. The levels are completely (Full), partially (Partial) or not at all (None).

To display the execution plan for filters

1. In the Command window, type SYS(3054,1) to enable SQL ShowPlan.

2. Type your SQL SELECT statement.

   For example, you might type:

   SELECT * FROM customer, orders ;
   AND Upper(country) = "MEXICO"
   

3. On the screen, read the execution plan.

   For this example, the screen might display:

   Using Index Tag Country to optimize table customer
   Rushmore Optimization Level for table customer: Full
   Rushmore Optimization level for table orders: none
   

4. In the Command window, type SYS(3054,0) to turn off SQL ShowPlan.

You can then pass 11 to the SYS function to evaluate joins in the FROM or WHERE clauses.

To display the execution plan for joins

1. In the Command window, type SYS(3054,11) to enable SQL ShowPlan.

2. Enter your SQL SELECT statement.

   For example, you might type:

   SELECT * ;
   FROM customer INNER JOIN orders ;
      ON customer.cust_id = orders.cust_id ;
   WHERE Upper(country) = "MEXICO"
   

3. On the screen, read the execution plan.

   For this example, the screen might display:

   Using Index Tag Country to optimize table customer
   Rushmore Optimization Level for table customer: Full
   Rushmore Optimization level for table orders: none
   Joining table customer and table orders using Cust_id
   

4. In the Command window, type SYS(3054,0) to turn off SQL ShowPlan.

Controlling Join Evaluation

If the execution plan for your joins does not match your specific needs, you can force your join order to execute exactly as written without optimization from the processor. To force the evaluation order of the join, you need to add the FORCE keyword and place your join conditions in the FROM clause. Join conditions placed within the WHERE clause are not included in a forced join evaluation.

Note   You can't use the FORCE keyword in SQL pass-through statements or remote views because this keyword is a Visual FoxPro extension of the ANSI standard and is not supported in other SQL dictionaries. The FORCE clause is global and therefore applies to all tables in the JOIN clause. Be sure that the order in which the join tables appear is exactly the order in which they should be joined. You can also use parentheses to control the evaluation order of joins.

In this example, the first join specified is also the first join evaluated. The Customer table is joined with the Orders table first. The result of that join is then joined with the OrdItems table:

SELECT * ;
   FROM FORCE Customers ;
   INNER JOIN Orders ;
      ON Orders.Company_ID = Customers.Company_ID ;
   INNER JOIN OrItems;
      ON OrdItems.Order_NO = Orders.Order_NO 

In this example, the join within the parentheses for the table Orders and OrdItems is evaluated first. The result of that join is then used in the evaluation of the join with Customers:

SELECT * ;
FROM FORCE Customers ;
   INNER JOIN (orders INNER JOIN OrdItems ;
      ON OrdItems.Order_No = Orders.Order_No) ;
      ON Orders.Company_ID = Customers.Company_ID

See Also

Setting the Maximum Number of Records Downloaded | Creating Queries | Sharing Connections for Multiple Remote Views | Testing a Connection for Busyness | SYS(3054)