Summary of performance considerations

 

You should consider these performance factors when developing queries that use distributed files.

  1. For the OPNQRYF command and the query API (QQQQRY), specifying ALWCPYDTA(*OPTIMIZE) allows each node to choose an index or a sort to satisfy the ordering specified.

  2. For the OPNQRYF command and the query API (QQQQRY), specifying ALWCPYDTA(*YES) or ALWCPYDTA(*NO) enforces that each node use an index that exactly matches the specified ordering fields. This is more restrictive than the way the optimizer processes ordering for nondistributed files.

  3. Adding an ORDER BY clause to a DISTINCT select can return records faster by not requiring a final sort on the requesting system.

  4. Including all of the fields of the partitioning key in the grouping fields generally results in one-step grouping, which performs better than two-step grouping.

  5. Including all of the fields of the partitioning key in the join criteria generally results in a collocated distributed join.

  6. Including all of the fields of the partitioning key in isolatable, equal record selection generally results in the query being processed on only one node.

  7. Including any of the following scalar functions in isolatable, equal record selection generally results in the query being processed on only one node:

    • NODENAME

    • NODENUMBER

    • PARTITION

 

Parent topic:

Query design for performance with DB2 Multisystem