Best Practices: Writing and Tuning Queries for Optimal Performance

	Introduction
	Outline for Best Practices paper
	Download
	Get involved

Learn best practices for minimizing the impact of SQL statements on DB2 database performance. This paper focuses on good fundamental writing and tuning practices that can be widely applied to help improve DB2 database 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.

Back to top

• Executive summary
• Introduction
• Writing SQL statements
• Avoid complex expressions in search conditions
• Avoid join predicates on expressions
• Avoid expressions over columns in local predicates
• Avoid data type mismatches on join columns
• Do not use no-op expressions in predicates to change the optimizer estimate
• Avoid non-equality join predicates
• Avoid multiple aggregations with DISTINCT keyword
• Avoid unnecessary outer joins
• Use OPTIMIZE FOR N ROWS clause with FETCH FIRST N ROWS ONLY clause
• If you are using the star schema join, ensure your queries fit the required criteria
• Avoid redundant predicates
• Designing and configuring your database
• Use constraints to improve query optimization
• Use the REOPT bind option with input variables in complex queries
• Choose the best optimization class for your workload
• Use parameter markers to reduce compilation time for dynamic queries
• Set DB2_REDUCED_OPTIMIZATION registry variable
• Gather accurate catalog statistics, including advanced statistics features
• Minimize RUNSTATS impact
• Avoid updating catalog statistics manually
• Use optimization profiles if other tuning options do not product acceptable results
• Tuning SQL statements using the explain facility
• Analyzing performance changes
• Evaluating performance tuning efforts
• Best practices
• Conclusion
• Further reading
• Contributors
• Notices
• Trademarks

Back to top

" Writing and Tuning Queries for Optimal Performance " (May 2008)
Learn best practices for minimizing the impact of SQL statements on DB2 database performance. This paper focuses on good fundamental writing and tuning practices that can be widely applied to help improve DB2 database performance. (pdf; 646KB; 38 pages)

Back to top

Comment, edit, or add your own insights to the Deep Compression best practices on the IBM Database Wiki.

Check out all the other Best Practices papers and see how you can improve your experience with DB2 for Linux, UNIX, and Windows.

Back to top