Queries

To determine whether a query is running inefficiently, follow these steps:

Run and time the query, preferably on an OnLine system with little or no activity.
Run the query with SET EXPLAIN ON and check the SET EXPLAIN output (in the sqexplain.out file) to determine if the optimizer is choosing the best path.
If you can determine that the optimizer is not choosing the most efficient path, take steps to change it. Even though you cannot specifically change the query path, there are steps you can take to influence the optimizer: - Add and drop indexes on query columns. - Create data distributions on query columns. - Use SET OPTIMIZATION LOW to decrease the optimization time. - Evaluate the structure of your SQL statement. Re-write your query, if necessary. For example, you may be able to change a sub-query to a join. - On the later engines you can use Optimiser directives
After you make a change, re-run and time the query to make sure the change you made affected the query positively.

One of the most common problems administrators have on newly created databases is to forget to run UPDATE STATISTICS. If you do not run UPDATE STATISTICS, the optimizer will have no way to make a correct decision on the optimizer path. Queries that may normally take seconds to run after UPDATE STATISTICS has updated the statistics may take minutes or even hours to run if UPDATE STATISTICS has not been run.

You may run UPDATE STATISTICS daily or weekly or after specific applications, depending upon the type of activity that occurs on your system. For example, if an application deletes twenty percent (20%) of the rows in the table, you may want to run UPDATE STATISTICS after it completes. Some administrators run UPDATE STATISTICS nightly for convenience, especially if no activity occurs on the OnLine system at night.

Join Methods

Nested loop join	The nested loop join is generally the most efficient method when the join filters on both tables are indexed. The first table is scanned in any order, and the join column is matched with corresponding columns in the second table.
Sort merge join	The sort merge join requires that both tables (after filters have been applied) have been sorted in the order of the join column. If an index exists on the join column, no sort is necessary. The sort merge join will usually occur when one or both of the tables do not have an index on the join column.
Hash join	The hash join is a method of joining two tables starting in version 7.0. The second table is read and put into a hash table. The first table is scanned and matched against the hash table.

The OPTCOMPIND configuration parameter is one way to influence the optimizer choice in a query path. It can be set as a configuration parameter or environment variable. OPTCOMPIND is available starting in version 7.0 of INFORMIX-OnLine Dynamic Server. OPTCOMPIND simply determines when the optimizer is free to compare a dynamic join method with an existing index join method for a specific join pair only. Setting OPTCOMPIND to 0 forces the optimizer to behave as in earlier releases; namely, when examining a specific join pair, only consider a dynamic join method if there is no existing index which will can be used to accomplish the join.

Suppose you have two tables, A and B. You also have an index on B.x. You execute the following query:

select * from A, B where A.x = B.x;

The optimizer will consider TWO join orders: (A,B) and (B,A). When it considers (A,B), it will examine OPTCOMPIND and do one of the following: • OPTCOMPIND is 0: only consider the index join using the index on B.x. • OPTCOMPIND is 1: - If the transaction is in Repeatable Read mode, then only consider the index join using the index on B.x. - Otherwise choose the lowest cost between the index, hash, and sort merge joins. - OPTCOMPIND is 2: choose the lowest cost between the index, hash, and sort merge joins.

When the optimizer considers (B,A), since there are no useful indices on A, it will choose between a dynamically constructed index, hash, and sort merge join. Finally, the optimizer compares the cost of the path (A,B) with (B,A) and chooses the lower costing path. If the cost of path (B,A), which will be a dynamic join method, is less then the cost of (A,B), an index path, then the dynamic join path will be chosen.

An OLTP query can be described as one that examines a small number of rows. For example, a query to look up an account number and balance is an OLTP query.

A decision support query (DSS) is one that examines a large number of rows, and sometimes performs complex processing (such as GROUP BY, SUM, AVG, etc.). Because of the parallel processing that can be performed on these types of queries, the optimization of these queries may differ from OLTP queries.

The following guidelines for improving query times can be used as a starting point for examining the query path. There are exceptions to the guidelines. For example, large decision support queries (that examine a large number of rows) may perform better by using sequential scans because of the large number of rows that must be examined.

Avoid sorts for the ORDER BY clause. If possible, have an index on the column(s) in the ORDER BY statement. The optimizer will generally avoid a sort and use the index (although there will be exceptions).
Avoid hash and merge joins (OLTP only). These join methods have a significant overhead because of the sort files and hash tables that must be created. To avoid these types of joins, put an index on the join columns.
Avoid sequential scans. Although a sequential scan may be faster if a large number of rows must be read, for most small queries, a sequential scan is detrimental. To avoid a sequential scan, put an index on the filter column.
Usually, the optimizer places tables reducing the number of tuples first in the query path. By eliminating rows early in the query, less rows will have to be joined to the tables later in the query path. Usually, the optimizer chooses the most efficient query path. However, you can influence the optimizer by creating distributions and indexes on the query columns. Decision support queries can benefit from the Parallel Database Query feature starting in version 7.0 of INFORMIX-OnLine Dynamic Server. Because PDQ paralyzes some operations to make them faster, the guidelines for improving query times are different than for OLTP queries:
Remove indexes. Generally, when scanning most of a large table, it is faster to scan a table without reading the index. If the optimizer is choosing an index read, you might try removing the index to see if performance improves.
Set OPTCOMPIND to 1. This option gives the optimizer more possible paths to consider. The optimizer will choose the lowest cost method between an index join, a hash join, or sort merge join.
Turn on Parallel Database Query using the PDQPRIORITY configuration and environment variable. PDQPRIOR1TY will be discussed in detail in a later chapter.
Fragment tables to invoke parallel scans for large tables.