Improving individual query performance

You can test, monitor, and improve queries.

Test queries using a dedicated test system
You can test a query on a system that does not interfere with production database servers. However, you must be careful, because testing queries on a separate system might distort your tuning decisions.
Display the query plan
Before you change a query, display its query plan to determine the kind and amount of resources that the query requires. The query plan shows what parallel scans are used, the maximum number of threads required, and the indexes used.
Improve filter selectivity
You can control the amount of information that a query evaluates. The greater the precision with which you specify the desired rows, the greater the likelihood that your queries will complete quickly.
Automatic statistics updating
The database server updates statistics automatically according to a predefined schedule and a set of expiration policies. The Auto Update Statistics (AUS) maintenance system identifies tables and indexes that require new optimizer statistics and runs the appropriate UPDATE STATISTICS statements to optimize query performance.
Update statistics when they are not generated automatically
The UPDATE STATISTICS statement updates the statistics in the system catalog tables that the optimizer uses to determine the lowest-cost query plan.
Improve performance by adding or removing indexes
You can often improve the performance of a query by adding or, in some cases, removing indexes. You can also enable the optimizer to automatically fetch a set of keys from an index buffer.
Optimizer estimates of distributed queries
The optimizer assumes that access to a row from a remote database takes longer than access to a row in a local database. The optimizer estimates include the cost of retrieving the row from disk and transmitting it across the network.
Improve sequential scans
You can improve the performance of sequential read operations on large tables by eliminating repeated sequential scans.
Enable view folding to improve query performance
You can significantly improve the performance of a query that involves a view by enabling view folding.
Reduce the join and sort operations
After you understand what the query is doing, you can look for ways to obtain the same output with less effort.
Optimize user-response time for queries
You can influence the amount of time that Informix takes to optimize a query and to return rows to a user.
Optimize queries for user-defined data types
Queries that access user-defined data types (UDTs) can take advantage of the same performance features that built-in data types use.
Optimize queries with the SQL statement cache
Before the database server runs an SQL statement, it must first parse and optimize the statement. Optimizing statements can be time consuming, depending on the size of the SQL statement.
Monitor sessions and threads
You can monitor the number of active sessions and threads and the amount of resources that they are using. Monitoring sessions and threads is important for sessions that perform queries as well as sessions that perform inserts, updates, and deletes.
Monitor transactions
You can monitor transactions to track open transactions and the locks that those transactions hold. You can use several onstat utility options to view transaction, lock, and session statistics.

Parent topic: Performance Guide

Related information

Tune the new version for performance and adjust queries