Advantage Developer Zone

 
 
 

Viewing the Advantage SQL Execution Plan

Tuesday, August 15, 2006

Advantage has several tools and features that can help you optimize your SQL statements for the best possible performance. Performance was a major focus for the Advantage 8.0 release and will continue to be a focus for future releases. A very useful tool when optimizing queries is the Advantage SQL Execution Plan, which provides the ability to view the steps that the Advantage SQL Engine will take when processing the query.

Queries and Table Indexes

The SQL engine uses indexes and Advantage Optimized Filters (AOFs) whenever possible to process the query. Ideally indexes will be available on every field used in the WHERE and ORDER BY clauses. However, the SQL engine can also create temporary indexes automatically to improve performance. Consider the following statement:

SELECT * FROM Customer WHERE State = ’OR’
ORDER BY [Last Name], [First Name]

The SQL engine would first try to order the data according to the Order By clause. It looks for an index on Last Name and First Name. If an appropriate index does not exist then a temporary index on Last Name;First Name would be created. To process the where clause, the SQL engine will build an AOF. If no index is available the AOF will not be optimized.

With this understanding of how the SQL engine uses indexes, lets discuss how to view the SQL Execution Plan.

Viewing the SQL Execution Plan

By adding the key words “SHOW PLAN FOR” before any SQL statement or SQL script, the SQL engine will return a table containing information on how the query will be executed. To display the plan for the SQL statement in the above example, the syntax would look like this:

SHOW PLAN FOR
SELECT * FROM Customer WHERE State = ’OR’
ORDER BY [Last Name], [First Name]

This statement returns a static cursor containing the following information: statement text, statement id, operator, arguments, estimated number of executions and warnings. The operator information returned is the key to optimizing the query, along with the additional information in the warnings field. The operator field contains one of the following operations: distinct, evaluate, group by, index scan, join, sort, table scan, union all, insert into, update, or delete.

Using example data, the above statement generates two warnings. The State field does not have an index and there is no index that matches the ORDER BY conditions. The warning for the Index Scan operator contains this information: “Filter Not Optimized. No index matching the ORDER BY clause. Temporary Index will be built”.

Using the Native SQL Window

Advantage Data Architect (ARC) can generate a graphical representation of the SQL Execution Plan. While working in the Native SQL window, highlight the SQL statement you wish to view the plan for and click the show plan button . ARC generates a graphical representation of the SQL Execution plan. An example of this graphical plan for our example query is shown below. Notice the red circles next to the Evaluator (Live) and Index Scan (Live) operations indicating that warnings have been reported.

Non-Optimized Query

Additional information can be seen by placing the mouse pointer over a particular item on the Execution Plan screen. Here are the details for the Index Scan before and after creating an index on the first and last name fields (Last Name;First Name).

Non-Optimized Detail Optimized Detail

When the red warning circles exist, you can right-click the item on the Execution Plan screen and select ‘Create Index’ to open the table designer and create the new index.

Now that we have created new indexes on the Customer table, we view the SQL Execution Plan again. Notice that the red circles are not displayed indicating a fully optimized query.

Optimized Query

Summary

Although the example in this article is very simple, it demonstrates the type of information you can obtain by viewing the Advantage SQL Execution plan. This information will help you get the best performance from your queries. Additional information about interpreting the results of the Advantage SQL Execution Plan is available in the Advantage Help files, (look for the “SQL Execution Plan” topic in the index).