IBM @server iSeries, User Manual

Page: 243 / 294

Appendix C. Query for iSeries performance tips and
techniques
This appendix provides guidelines for improving the performance of the Query for iSeries product. These
guidelines help you better understand how Query works and which key items to keep in mind for
performance when designing or changing a query.
This appendix does not discuss all variations of queries, but instead provides tips and techniques that help
with the majority of queries running on the iSeries system. You need to determine which tips and
techniques apply to your own particular queries.
The information in this appendix is divided into the following sections:
v
Introduction to Query Processing
v
File Definitions and Data
v
Defining Queries
v
Using Join Operations
v
Miscellaneous Tips and Techniques
v
Query Status Messages
Introduction to Query for iSeries query processing
Query processing involves the following stages:
v
Validating the query and evaluating the best method for retrieving the requested data
v
Performing the input/output (I/O) for this data
v
Presenting the data in the requested format
Query often overlaps these stages to provide the best possible response time.
In the first stage of running a query, called optimization , Query determines the fastest way to process a
query. An access plan results and is used to perform the actual I/O for the query.
Optimization includes factors such as file size, selection tests, and sort tests. However, the main
performance element for both optimization and I/O is the use of keyed sequence access paths for the files
selected by the query.
Keyed sequence access paths in Query for iSeries
A keyed sequence access path describes the order in which records in a database file are read. Use the
Create Logical File (CRTLF) command to create access paths with keys specified in the data description
specifications (DDS).
During optimization, Query uses existing keyed sequence access paths to obtain an approximation of the
number of records the query will return. This information is needed in the optimization itself. Also, Query
uses existing access paths, if possible, to do the required I/O. Depending on the circumstances, Query
may choose to build a temporary access path to complete the query request.
Without existing access paths, Query either must read every record in each file to determine if it meets the
values in the query or build a temporary access path if Query requires one. These options can be
expensive in terms of processing unit, I/O, and storage requirements and can result in longer response
times.
© Copyright IBM Corp. 2000, 2002
231