Open Query File (OPNQRYF) command: Performance considerations

 

Here are the tips and techniques for optimizing the performance of the Open Query File (OPNQRYF) command.

The best performance can occur when the OPNQRYF command uses an existing keyed sequence access path. For example, if you want to select all the records where the Code field is equal to B and an access path exists over the Code field, the system can use the access path to perform the selection (key positioning selection) rather than read the records and select at run time (dynamic selection). The OPNQRYF command cannot use an existing index when any of the following conditions are true:

Part of the OPNQRYF processing is to determine what is the fastest approach to satisfying your request. If the file you are using is large and most of the records have the Code field equal to B, it is faster to use arrival sequence processing than to use an existing keyed sequence access path. Your program still sees the same records. The OPNQRYF processing can only make this type of decision if an access path exists on the Code field. In general, if your request includes approximately 20% or more of the number of records in the file, the OPNQRYF processing tends to ignore the existing access paths and read the file in arrival sequence.

If no access path exists over the Code field, the program reads all of the records in the file and passes only the selected records to your program. That is, the file is processed in arrival sequence.

The system can perform selection faster than your application program. If no appropriate keyed sequence access path exists, either your program or the system makes the selection of the records you want to process. Allowing the system to perform the selection process is considerably faster than passing all the records to your application program.

This is especially true if you are opening a file for update operations because individual records must be passed to your program, and locks are placed on every record read (in case your program needs to update the record). By letting the system perform the record selection, the only records passed to your program and locked are those that meet your selection values.

If you use the KEYFLD parameter to request a specific sequence for reading records, the fastest performance results if an access path already exists that uses the same key specification or if a keyed sequence access path exists that is similar to your specifications (such as a key that contains all the fields you specified plus some additional fields on the end of the key). This is also true for the GRPFLD parameter and on the to-fields of the JFLD parameter. If no such access path exists, the system builds an access path and maintains it as long as the file is open in your job.

Processing all the records in a file by an access path that does not already exist is generally not as efficient as using a full record sort, if the number of records to be arranged (not necessarily the total number of records in the file) exceeds 1000 and is greater than 20% of the records in the file. While it is generally faster to build the keyed sequence access path than to do the sort, faster processing allowed by the use of arrival sequence processing normally favors sorting the data when looking at the total job time. If a usable access path already exists, using the access path can be faster than sorting the data. You can use the ALWCPYDTA(*OPTIMIZE) parameter of the Open Query File (OPNQRYF) command to allow the system to use a full record sort if this is the fastest method of processing records.

If you do not intend to read all of the query records and if the OPTIMIZE parameter is *FIRSTIO or *MINWAIT, you can specify a number to indicate how many records you intend to retrieve. If the number of records is considerably less than the total number the query is expected to return, the system might select a faster access method.

If you use the grouping function, faster performance is achieved if you specify selection before grouping (QRYSLT parameter) instead of selection after grouping (GRPSLT parameter). Only use the GRPSLT parameter for comparisons involving aggregate functions. For most uses of the OPNQRYF command, new or existing access paths are used to access the data and present it to your program. In some cases of the OPNQRYF command, the system must create a temporary file. The rules for when a temporary file is created are complex, but the following cases are typical in which this occurs:

When a dynamic join occurs (JDFTVAL(*NO)), the OPNQRYF command attempts to improve performance by reordering the files and joining the file with the smallest number of selected records to the file with the largest number of selected records. To prevent the OPNQRYF command from reordering the files, specify JORDER(*FILE). This forces the OPNQRYF command to join the files in the order specified on the FILE parameter.

 

Parent topic:

Using Open Query File (OPNQRYF) command

 

Related concepts


Database performance and query optimization