Tuning Your System for CA Dataquery › Tuning Queries

Tuning Queries

Your queries will run more efficiently if you consider the following suggestions when you build your queries.

Key Usage

Keys minimize processing. Therefore, you can build keys to improve Compound Boolean Selection Facility performance.

When the predicates of a query uses keys, Compound Boolean Selection Facility can use the CA Datacom/DB index to evaluate the predicates more efficiently and can avoid having to access data rows. When the predicates includes no keys, Compound Boolean Selection Facility must examine the data rows in the table to evaluate the predicate.

Predicate Structure

Use predicates that can be easily converted to Compound Boolean Selection Facility format. There are predicates that are difficult or impossible to convert to Compound Boolean Selection Facility format. These are handled by CA Dataquery. The predicates that are not passed to the Compound Boolean Selection Facility are:

• Predicates in which a key is compared to a value:
  • And the value is masked and the comparison operator is neither equal or not equal
  • And the comparison is for CONTAINING and the key has multiple noncontiguous columns
• Predicates in which a column is compared to a value and the value is masked and the comparison operator is neither equal or not equal.
• Predicates in which a key is compared to a column and the data type of the column is not character, zoned, or pure double byte characters.
• Predicates in which a key is compared to a key and one or both of the keys has noncontiguous component columns and the keys do not have the same number and length of component columns.
• Predicates in which a column is compared to a column:
  • And the columns are of different data types
  • And the columns are numeric but have different precisions
• Predicates which include an arithmetic expression.

When a predicate that cannot be converted to Compound Boolean Selection Facility format is ANDed to a predicate that can be converted, the predicate that can be converted is passed to Compound Boolean Selection Facility. The predicate that cannot be converted is processed by CA Dataquery and then the results from Compound Boolean Selection Facility and CA Dataquery are ANDed.

When the predicates are ORed, if any predicate cannot be processed by the Compound Boolean Selection Facility, none of the ORed predicates can be processed by the Compound Boolean Selection Facility.

Example:

A FIND statement has three predicates: P1, P2, and P3. P3 cannot be converted to Compound Boolean Selection Facility format.

• WITH P1 AND P2 AND P3

  In this case, since there are no ORs, P1 and P2 are passed to the Compound Boolean Selection Facility and P3 is then applied to the result set.

• WITH P1 AND (P2 OR P3)

  In this case, P2 cannot be processed by Compound Boolean Selection Facility along with P3 since they are ORed, so only P1 is passed to the Compound Boolean Selection Facility. The condition P2 OR P3 is then applied to the result set from Compound Boolean Selection Facility.

Join Column Selection

When two tables are related in a query, they are normally joined by columns or keys in the two rows. When the search is performed, the link column in the table searched secondly is used to create an additional key-value or column-value predicate using a value extracted from the link column of the first table. With CA Dataquery you can join tables when the link column is not a key in either table. This should be done with care because if there are no keyed criteria supplied for the table searched secondly in the relationship, repeated full table searches of that table can be required.

When multiple tables are related in a query, CA Dataquery does not necessarily search the tables in the order stated. Instead, it determines every possible way that the search can be done, dynamically estimates the cost of each, and selects the lowest cost estimate.

Important! The use of outer joins and disjoins in a query causes the CA Dataquery optimization process to be bypassed. CA Dataquery processes any query containing either an outer join or a disjoin keyword by accessing the database tables in the order they appear within the query. The use of OUTER-JOIN, RIGHT-JOIN, or RIGHT-DISJOIN adds additional time to the processing of the FIND statement since CA Dataquery must read each table named on the "right side" twice to accomplish the join. The use of LEFT-JOIN or LEFT-DISJOIN does not cause this additional processing.

Usage Procedures

Establish procedures for users to follow to realize resource conservation:

• For a new query, limit the initial search to test the query and check the results.

  For example, this can be done by specifying FIND 20 ROWS instead of FIND ALL ROWS.

• Include criteria in the query that uses a key or the first column of a key to limit the scope of the search.
  • When CA Dataquery cannot use keyed access, the entire table is read sequentially.
  • Make users aware of the DISPLAY command as a tool to display key definitions.