How to: Optimize Filters and Joins

Article
06/24/2008

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

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

Type your SQL SELECT statement.

For example, you might type:

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

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

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

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

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"

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

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

Add the FORCE keyword and place your join conditions in the FROM clause.

Note

Note 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

Share via

How to: Optimize Filters and Joins

To display the execution plan for filters

To display the execution plan for joins

Controlling Join Evaluation

To force the evaluation order of the join

See Also

Tasks

Reference

Additional resources