For general information on the SQL query plan, refer to Articles:
The structure of the SQL-92 virtual system table _Sql_Qplan How to run a SQL-92 query to gather performance information against a SQL statement When investigating a performance issue, look for the following statements in the SQL Query Plan:
RESTRICT
TABLE SCAN
DYNAMIC INDEX
SORTThe less the above keywords appear in a Query Plan, better the performance is likely to be.
RESTRICT usually indicates that an entire table is loaded into memory, because there was no relevant index. If an index exists, only data that satisfies the query will be loaded into memory.
TABLE SCAN means that the optimizer could not find suitable index to access the table. This could be because there is no WHERE clause or no index on a column used in WHERE clause.
DYNAMIC INDEX is usually an indicator that a temporary table is created because a specific index is needed to perform a join operation with a different table.
SORT means that there is sort operation that Progress performs because it doesn't find a relevant index. SORT is the most difficult to eliminate when using SORT or GROUP BY in the query.
In all cases, the goal is to create suitable index(s) to eliminate these operations. Please note that there are situations when some of these cannot be prevented.
