This appendix lists guidelines to ensure the efficient performance of Query Processor.
Query Plan Report
Query Processor provides a query plan report showing the basic steps that are followed to resolve the query. You can activate this report using the Query Design Center from the Options menu by selecting Qgraphs.
Default Isolation Level
The default isolation level is Read Committed which results in an Enterprise Database Server SECURE lock and unlock for each referenced record. This is done to ensure that the referenced records are not involved in an active transaction. If the transaction state of the records is not important to the query, then specify Read Uncommitted.
SQL Views
Enterprise Database Server sets and subsets are modeled as SQL views. Using these views can force the use of those sets or subsets over the selection that is made by the Query Processor query optimizer. However, you should avoid the use of these views if the high-order key is not an EQL condition as that would force a linear search through the set or subset which can adversely affect processing time and CPU usage.
Linear Searches
A linear search on a large data set can adversely affect processing time and CPU usage. Using a subset reference can limit the search if the referenced records are within the scope of the WHERE clause and the population of the subset is less than 20 percent of the data set population (a more specific goal is the result of the equation subset population LEQ data set population / blocking factor).
A linear search on a set can also can adversely affect processing time and CPU usage if the referenced data set is not reorganized through the set (that is, in the same order as the set). A set linear search can be avoided by specifying an OR condition that cannot be satisfied (for example, c1>999999).
WHERE clause
A WHERE clause that includes conditions other than EQL cause a full linear search or partial search, depending on other conditions. The following are examples of such conditions:
-
A condition that is not a key always results in a full linear search.
-
If a key condition is specified without an OR condition, the search proceeds from the beginning of the index (LSS, LEQ) or from the key value (GTR, GEQ) to the end of the index.
-
An OR condition with one relation that is not a key results in a full linear search.
-
A NEQ condition always results in a linear search.
-
Using BETWEEN limits the search range.
-
A condition on an embedded data set that does not include the foreign key of the master data set requires a full linear search of the master data set.
Enterprise Database Server Subsets
Query Processor does not optimize queries by using Enterprise Database Server subsets. However, you can force the use of a subset by referencing the subset name instead of the data set name.
| Note: | Condition testing is limited to only those rows that are referenced by the subset. |
Enterprise Database Server RANDOM and DIRECT data sets require a linear search for a key relation other than EQL.
Joins can adversely affect processing time and CPU usage. Query Processor retrieves the referenced records into temporary files and then sorts and merges the records to generate the result set.
