Processing queries: Overview

 

This overview of the query optimizer provides guidelines for designing queries that will perform and will use server resources more efficiently. This overview covers queries that are optimized by the query optimizer and includes interfaces such as SQL, OPNQRYF, APIs (QQQQRY), ODBC, and Query/400 queries. Whether you apply the guidelines, the query results will still be correct.

The information in this overview is complex. You might find it helpful to experiment with an iSeries™ server as you read this information to gain a better understanding of the concepts.

When you understand how DB2 Universal Database™ for iSeries processes queries, it is easier to understand the performance impacts of the guidelines discussed in this overview. There are two major components of DB2 Universal Database for iSeries query processing:

• How the server accesses data.

  These methods are the algorithms that are used to retrieve data from the disk. The methods include index usage and row selection techniques. In addition, parallel access methods are available with the DB2^® UDB Symmetric Multiprocessing operating system feature.

• Query optimizer.

  The query optimizer identifies the valid techniques which can be used to implement the query and selects the most efficient technique.

• How the query optimizer makes your queries more efficient
  Data manipulation statements such as SELECT specify only what data the user wants, not how to retrieve that data. This path to the data is chosen by the optimizer and stored in the access plan. You should understand the techniques employed by the query optimizer for performing this task.

• General query optimization tips
  Here are some tips to help your queries run as fast as possible.

• Access plan validation
  An access plan is a control structure that describes the actions necessary to satisfy each query request. It contains information about the data and how to extract it. For any query, whenever optimization occurs, the query optimizer develops an optimized plan of how to access the requested data.

• Single table optimization
  At run time, the optimizer chooses an optimal access method for the query by calculating an implementation cost based on the current state of the database. The optimizer uses 2 costs when making decisions: an I/O cost and a CPU cost. The goal of the optimizer is to minimize both I/O and CPU cost.

• Join optimization
  A join operation is a complex function that requires special attention in order to achieve good performance. This section describes how DB2 Universal Database for iSeries implements join queries and how optimization choices are made by the query optimizer. It also describes design tips and techniques which help avoid or solve performance problems.

• Distinct optimization
  Distinct is used to compare a value with another value.

• Grouping optimization
  DB2 Universal Database for iSeries has certain techniques to use when the optimizer encounters grouping. The query optimizer chooses its methods for optimizing your query.

• Ordering optimization
  This section describes how DB2 Universal Database for iSeries implements ordering techniques, and how optimization choices are made by the query optimizer. The query optimizer can use either index ordering or a sort to implement ordering.

• View implementation
  Views, derived tables (nested table expressions or NTEs), and common table expressions (CTEs) are implemented by the query optimizer using one of two methods.

• Materialized query table optimization
  Materialized query tables (MQTs) (also referred to as automatic summary tables or materialized views) can provide performance enhancements for queries.

• Recursive query optimization
  Certain applications and data are recursive by nature. Examples of such applications are a bill-of-material, reservation, trip planner or networking planning system where data in one results row has a natural relationship (call it a parent, child relationship) with data in another row or rows. Although the kinds of recursion implemented in these systems can be performed by using SQL Stored Procedures and temporary results tables, the use of a recursive query to facilitate the access of this hierarchical data can lead to a more elegant and better performing application.

 

Parent topic:

Performance and query optimization