Kbase P62658: What to look for in SQL-92 Query Plan for performance issue?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/20/2009 |
|
Status: Verified
GOAL:
What to look for in SQL-92 Query Plan for performance issue?
GOAL:
What are the keywords used on SQL-92 Query Plan that can indicate performance issues?
GOAL:
How to interpret SQL-92 Query Plan
GOAL:
How to interpret the results of _Sql_QPlan
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
FIX:
Firstly, for general information on SQL query plan, see solutions 21676, "What is _Sql_Qplan?" , 20327, "How to display query plan for a SQL statement?" and 20007, "How to Find The Index Used in a SELECT Statement in SQL-92"
When investigating a performance problem, look for the following statements in SQL Query Plan:
RESTRICT
TABLE SCAN
DYNAMIC INDEX
SHORT
Less you have the above keywords in the Query Plan, better the performance is.
RESTRICT usually indicates that an entire table is loaded into memory, because there was no relevant index. If index exist, only data that satisfies the query will be loaded into memory.
TABLE SCAN means that the optimizer doesn't 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.
DYN.INDEX is usually the indicator that a temporary table is created because we need a specific index to perform the join operation with a different table.
SHORT mean again that there is sort operation that a Progress performs because it doesn't find the relevant index. SORT is the most difficult to elimate when using SHORT or GROUP BY.
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.