Query optimization strategy for INFORMIX online database

informix-online Dynamic Server (ids,informix-online) is the core of Informix database product technology, with its dynamic scalable architecture, efficient parallel processing capability, Shared memory technology and ease of management features, the hardware resources to play incisively and vividly. At present, IDs V7 is widely used in the key systems of China's financial, postal and telecommunication industries, with the continuous deepening of the application, the accumulation of data, the complexity of queries, the query speed will be slow, resulting in too long response time. Many users attribute it to the cause of the hardware, so upgrades, or reinvestment, the database tuning does not attract enough attention. This is actually a waste, with foreign developed countries light hardware, the idea of heavy application is just the opposite. The practice shows that the unreasonable configuration and improper optimization of the database are the main factors that degrade the performance. The implementation of the database management and maintenance of IDs, performance tuning is the main work of the system administrator, and whether the good query response embodies the performance of the database, so it is also the focus of tuning. From the perspective of system management, we can set up multiple clues, allocate shared memory space rationally, establish database and table distribution and fragment management to speed up the query speed, but ultimately based on the comprehensive understanding of the database itself, because the data is in constant change and accumulation, and as the application of in-depth query will become increasingly complicated. This paper expounds several query optimization strategies from the point of view of data management, and has a good effect in practical application, which is described as follows.

I. Classification and requirements of inquiries

The query for the number of database tables involved in the statement can be divided into: single-table query, multiple table query, joint query, subquery, and so on, multiple table query based on the connection of multiple tables, nested loops, merging sort, hash connection three ways, the most complex, but also the focus of tuning. At present, the application of database is divided into two types of online transaction processing (oltp,online Transaction processing) and decision support System (Dss,decision Support System), which have different requirements for query. OLTP mainly involves a single table, simple SQL statement, the data is read by index, the number of query rows is few, the response time requirement is very harsh, often in the second or below, more for online real-time business; The DSS involves the connection query between multiple tables, the SQL statement is complex, the data is read in physical order, the number of query rows is long. , and is used for complex data analysis based on data Warehouse technology. But in any case, we want the quickest response speed, which is the ultimate goal of tuning.

Second, the optimization strategy of the query

1. Make full use of the query optimizer

The query optimizer provides the optimization strategy analysis and selection method of the data query, by setting the relevant parameters, the optimizer can select the best connection strategy and find an optimal path in all the query paths. Choosing a good path is a vital part of query optimization, and a good path can scan for the fewest records and get the correct query results with minimal disk I/O. You can do this by following these steps.

(1) Set the connection policy

Implemented by modifying the Optcompind parameter values in the configuration file $onconfig.

OPTCOMPIND 0: The optimizer selects only index connections in the connection.

OPTCOMPIND 1: If the transaction is processed as a repeatable read mode (repeatable read), the index policy is selected, otherwise the optimizer automatically chooses the least expensive connection policy.

OPTCOMPIND 2: The optimizer automatically selects the least expensive connection strategy. You should choose this parameter as much as possible.

(2) Set the mode of query optimization

The optimal query path is also chosen by executing the following SQL statement, in the form of:

SET Optimization [High | Low | First_rows | All_rows]

In which, High is the default option, which means that all query paths are detected and selected optimally.

Low indicates that the depth-first method is used only in some paths to select the best, that is, in each connection comparison, the optimal path will continue to deepen and filter out the non optimal path, characterized by a short optimization time, but the path accuracy is low.

First_rows and All_rows are new options that have been added since IDs V7.3, and are useful for both OLTP and DSS. Traditional queries (that is, all_rows) output all the query results to the shared memory buffer at once, and the time consuming is very large, but practice shows that most users focus only on the first few screen outputs, so the first_rows option provides us with a good choice. The first_rows directs the optimizer to select a query path that outputs only the number of records that fill a buffer, and continues execution if the user continues with the query, thus avoiding unnecessary output and time wasting, and greatly increasing the speed of the query.

Thus, the rich function of the optimizer provides us with a flexible means, the administrator can choose the best way according to different applications, both to achieve the best query results, but also to minimize the resulting system overhead.