When writing queries, be aware of the general CA Dataquery performance considerations discussed in this section. CA Dataquery uses the CA Datacom/DB Compound Boolean Selection Facility to evaluate the row selection criteria in a DQL Mode query's FIND statement. CA Dataquery and the Compound Boolean Selection Facility dynamically handle the search optimization.
CA Dataquery uses predicates (a WITH in a DQL Mode query FIND statement) to build a Compound Boolean Selection Request Qualification Area (RQA), converting the selection criteria in a query's statements into the Compound Boolean Selection format.
Consider the following when using predicates in queries that use:
Use of Keys
It is important to use predicates with keys as much as possible. The Compound Boolean Selection can utilize the CA Datacom/DB index rather than searching rows to evaluate the selection criteria of a query.
In the case where predicates with only nonindexed columns are used, Compound Boolean Selection must examine each data row in the table to evaluate the predicates (full table search). Performance can be enhanced with the use of predicates with keys. There is some overhead involved with maintaining keys on a table. This must be weighed against the potential of performance improvements in CA Dataquery.
Signed Numeric Data
When the predicates in a query statement refer to columns containing signed numeric data of packed or zoned decimal format, it is important to be as specific as possible with the CA Datacom Datadictionary TYPE-NUMERIC attribute. For example, by specifying P for the TYPE-NUMERIC attribute when the data that a query is accessing contains only positive values, enables CA Datacom/DB's Compound Boolean Selection to search the index for entries with only positive values, because the traversal key value range is restricted.
Note: The TYPE-NUMERIC attribute must, however, agree with the actual data or incorrect results can be obtained. (Check with your Database Administrator for more information.)
Nonconvertible Predicates
Most predicates that can be used in a query can be easily converted to Compound Boolean Selection format, however, there are some special cases that are impossible to convert. These special cases are referred to as nonqualifying predicates. These nonqualifying predicates are handled outside of the Compound Boolean Selection.
The following types of DQL Mode predicates are passed to the Compound Boolean Selection.
When the data type of the column is character or zoned.
When masking is used, the comparator must be either EQ or NE. When containing is used in a compare, the columns which comprise the key must be contiguous.
When masking is used, the comparator must be EQ or NE. If the field is numeric it must have no more than 15 digits, and if masking is used the field must not be packed decimal.
When one or both of the keys are comprised of discontiguous columns, the two keys must have the same number and length of component fields.
The columns must be of the same data types. If the columns are numeric they must have the same precision and if they are numeric must be of no more than 15 decimal digits.
Note: Arithmetic expressions in predicates do not qualify.
It is possible for predicates that would normally be passed to Compound Boolean Selection to be disqualified because they were connected by an OR to nonqualifying predicates. For example, in the following query NAME does not qualify because the comparator is GTE, whereas DIAG-COD and CITY-STATE-ZIP would qualify.
FIND DA19-PATIENT WITH NAME GTE 'C#' AND DIAG-COD EQ ' ' AND CITY-STATE-ZIP = 'DALLAS#' PRINT NAME DIAG-COD CITY-STATE-ZIP
For example, if NAME and DIAG-COD were indexed columns and CITY-STATE-ZIP were nonindexed:
The data that met all three qualifications would be found.
In the next example:
FIND DA19-PATIENT (NAME GTE 'C#' OR DIAG-COD EQ ' ') AND CITY-STATE-ZIP = 'DALLAS#' PRINT NAME DIAG-COD CITY-STATE-ZIP
Multiple Table Queries
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 second table that is searched is used to create an additional KEY-VALUE or COLUMN-VALUE predicate using a value extracted from the link-column in the table that is searched first.
CA Dataquery allows tables to be joined when the link-column is not indexed in either of the tables. This should be done with care if the second table in the relationship to be searched does not have key criteria. Compound Boolean Selection could be forced to perform repeated full searches on that table.
When multiple tables are related in a query, CA Dataquery determines every possible way that the search could be done, dynamically estimates the cost of each and selects the lowest cost strategy.
FIND ALL CA-ACCTS-REC
PRINT TITLE1 'SAMPLE ORDER ENTRY DATABASE'
TITLE2 'ACCOUNTS TABLE'
ORD-ID
BILL-DT
ORD-AMT
In the previous DQL Language example, no predicates are used. Compound Boolean Selection reads the index in native key sequence for CA-ACCTS-REC and returns the row IDs to CA Dataquery. The data rows were not accessed during processing of the FIND statement.
|
Copyright © 2014 CA.
All rights reserved.
|
|