Writing and Tuning Queries for Optimal Performance

This document describes the best practices for minimizing the impact of SQL statements on DB2 database performance. You can minimize this impact in several ways:

• By writing SQL statements that the DB2 optimizer can more easily optimize. The DB2 optimizer might not be able to efficiently run SQL statements that contain non-equality join predicates, data type mismatches on join columns, unnecessary outer joins, and other complex search conditions.

• By correctly configuring the DB2 database to take advantage of DB2 optimization functionality. The DB2 optimizer can select the optimal query access plan if you have accurate catalog statistics and choose the best optimization class for your workload.

• By using the DB2 explain functionality to review potential query access plans and determine how to tune queries for best performance.

This document includes best practices that apply to general workloads, warehouse workloads, and SAP workloads.
There are a number of ways to deal with specific query performance issues after an application is written. However, this document focuses on good fundamental writing and tuning practices that can be widely applied to help improve DB2 database performance.

If you follow the recommendations discussed in this document and still experience poor query performance, there are a number of techniques available to understand why. The "Tuning and Monitoring Database System Performance" best practices paper describes a number of techniques for identifying performance problems and ways the system can be configured to help prevent them. The "Physical Database Design" best practices paper describes how to use DB2 database system features such as multi-dimensional clustering (MDC), materialized query tables (MQTs), and the DB2 Design Advisor to achieve optimal query performance. A subsequent best practices paper will describe techniques to analyze performance problems with a specific query.

For suggestions on improving XQuery performance, see the "Managing XML Data" best practices paper.

Table of contents

Best Practice - Writing and Tuning Queries for Optimal Performance - 1. Introduction

Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements

• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.01. Avoid complex expression in search conditions
• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.02. Avoid join predicates on expressions
• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.03. Avoid expression over columns in local predicates
• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.04. Avoid data type mismatches on join columns
• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.05. Do not use no-op expression in predicates to change the optimizer estimate
• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.06. Avoid non-equality join predicates
• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.07. Avoid multiple aggregations with the DISTINCT keyword
• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.08. Avoid unnecessary outer joins
• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.09. Use OPTIMIZATION FOR N ROWS clause with FETCH FIRST N ROWS ONLY clause
• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.10. If you are using the star schema join, ensure your queries fit the required criteria
• Best Practice - Writing and Tuning Queries for Optimal Performance - 2. Writing SQL statements - 2.11. Avoid redundant predicates

Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database

• Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.1. Use constraints to Improve Query Optimization
• Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.2. Use the REOPT bind option with input variables in complex queries
• Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.3. Choose the best optimization class for your workload
• Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.4. Use parameter markers to reduce compilation time for dynamic queries
• Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.5. Set DB2_REDUCED_OPTIMIZATION registry variable
• Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.6. Gather accurate catalog statistics, including advanced statistics features
  • Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.6. Gather accurate catalog statistics, including advanced statistics features - 3.6.1. Column group statistics
  • Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.6. Gather accurate catalog statistics, including advanced statistics features - 3.6.2. Sub-element statistics
  • Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.6. Gather accurate catalog statistics, including advanced statistics features - 3.6.3. Statistical views
• Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.7. Minimize RUNSTATS impact
• Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.8. Avoid updating catalog statistics manually
• Best Practice - Writing and Tuning Queries for Optimal Performance - 3. Designing and configuring your database - 3.9. Use optimization profiles if other tuning options do not produce acceptable results

Best Practice - Writing and Tuning Queries for Optimal Performance - 4. Tuning SQL statements using the explain facility

• Best Practice - Writing and Tuning Queries for Optimal Performance - 4. Tuning SQL statements using the explain facility - 4.1. Analyzing performance changes
• Best Practice - Writing and Tuning Queries for Optimal Performance - 4. Tuning SQL statements using the explain facility - 4.2. Evaluating performance tuning effects

Best Practice - Writing and Tuning Queries for Optimal Performance - 5. Conclusion

Best Practice - Writing and Tuning Queries for Optimal Performance - 6. Best Practices Summary