How To
Summary
Brand new or established queries, running on the IBM i operating system, may experience slow response times. This document provides query tuning resources to the database engineer.
Objective
The following suggestions are intended to be a starting point for addressing a poor performing query identified by the DBE (database engineer) or your support representative.
Environment
Query applications executing on Db2 for IBM i platform (such as Query Management queries, HLL with embedded SQL, JDBC/ODBC/DRDA applications etc.)
Steps
Data manipulation statements specify only what data the user wants, not how to retrieve that data. The path, to the data, is chosen by the cost-based optimizer and stored in an access plan for (potential) re-use. While you cannot control which path the cost-based optimizer takes, you can significantly influence an optimal path.
The optimizer is predictive and selects from a list of plans based on many parameters; each parameter can change the estimated cost of the plan. The key to consistently good performance is to ensure the parameters are understood and accurately represent the query.
Two of the most fundamental ways to achieve good performance
- Collect background statistics (DSPSYSVAL SYSVAL(QDBFSTCCOL)).
- Create indexes for highly selective queries that return a small number of rows
Perfect Radix Index Guidelines
When evaluating data access methods for queries, it is best to create radix indexes with keys that match the query's local selection and join predicates. The goal is to create indexes whose leftmost key columns match your selection and join predicates. Indexes help supply the optimizer with estimated number of matching rows and it is these estimates that help the optimizer determine the fastest path to the data.
As a general rule, order the key columns for an index in the following way:
Equal predicates first. That is, any predicate that uses the = operator may narrow down the range of rows the fastest and should therefore be
first in the index.
If all predicates have an equal operator, then order the columns as follows:
− Selection predicates + join predicates
− Selection predicates + join predicates
− Selection predicates + group by columns
− Selection predicates + order by columns
− Order by columns + selection predicates
In addition to the guidelines, in general, the most selective key columns should be placed first in the index. Assume that a query has local
selection that references the part_id and part_type columns and that part_id is the primary key column for the parts table. Based on this
information, the partid would be listed first in the key definition because it will only select one row while the part_type column most likely
selects 5 to 20% of the rows.
For examples, see page 24 of IBM's Indexing and Statistics Strategies document.
Environmental Best Practices
- Do not use symmetric multiprocessing for transactional queries (OLTP), reserve SMP for batch reporting environments.
- Set the optimization goal to reflect how the result set will actually be processed; use OPTIMIZE FOR n ROWS on your query when possible.
- Stay current on database fixes
- If your queries often use table scan, use the Reorganize Physical File Member (RGZPFM) command to remove deleted rows from tables, or the Change Physical File (CHGPF) REUSEDLT (*YES) command to reuse deleted rows.
- Specify ALWCPYDTA(*OPTIMIZE) to allow the query optimizer to create temporary copies of data so better performance can be obtained. The IBM® i Access ODBC driver and Query Management driver always use this mode.
- For SQL, use CLOSQLCSR(*ENDJOB) or CLOSQLCSR(*ENDACTGRP) to allow open data paths to remain open for future invocations.
- Use ALWBLK(*ALLREAD) to allow row blocking for read-only cursors.
- Do not test query performance in a non-SQL performance interface such as STRSQL
- (STRSQL is a convenient way to test syntax or create indexes but it by-passes important query optimizer settings and therefore does not accurately represent a true SQL environment). Instead, test performance in Access Client Solutions (ACS) Run SQL Scripts environment, Query Manager (STRQM) or Query Management (CRTQMQRY).
Best Practices for Indexing
- Replace Maintained Temporary Indexes (MTIs) with permanent indexes
- Do not build all of the indexes advised by the Index Advisor, simply because they are advised. Instead, combine the knowledge of the above Indexing Methods and Strategy document with the advice produced by the Index Advisor, especially for complex queries.
Best Practices for Writing Queries
- Avoid the use of derivations in predicates (in the WHERE clause or ON clause). If you must use a derivation, consider creating a derived index, or, if possible, correct data model issues. For example, avoid SUBSTR(Col1, 1 ,5) = Col2
- Avoid the use of CASE statements in a predicate (as this impedes the optimizer from obtaining a good estimate on how many rows will be returned)
- Reduce or eliminate reliance on QTEMP library
- Use CTEs (common table expression) or subselects to split up complex query logic
- Minimize extraneous mapping by specifying only columns of interest on the query. For example, specify only the columns you need to query on the SQL SELECT statement instead of specifying SELECT *. Also, specify FOR FETCH ONLY if the columns do not need to be updated.
Additional Information
IBM i performance assessments and training opportunities are available to help you review or implement the above suggestions:
ibmsls@us.ibm.com
Related Information
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CKdAAM","label":"Performance-\u003EDatabase Performance"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
24 October 2024
UID
ibm16208434