Statistics

Probably the most commonly overlooked performance improvement is accurately maintaining the table statistics that the optimiser requires to build the query plan. There are freely available scripts, both on this site and others, that ensure that the statistics are maintained as per Informix's recommendations.

Run UPDATE STATISTICS MEDIUM with the default resolution and confidence for all columns that are not indices. The sample size for each column with the default resolution and confidence is 2962 rows. For a database with many columns, you may want to run UPDATE STATISTICS MEDIUM on the entire database. Next, run UPDATE STATISTICS HIGH for each column that heads an index. The time to run UPDATE STATISTICS should be relatively short since the index (as long as it is ascending) will be used to read the data; no sort will be executed. In most cases, this strategy should yield a good enough sample size for the optimizer to pick the correct path for most queries. If there is a problem query (one which you perceive to be running slower than it should), you should take the following steps:

1. First, run the query with SET EXPLAIN ON to record the query plan.
2. Then run UPDATE STATISTICS HIGH for the columns listed in the WHERE clause.
3. Run the query with SET EXPLAIN ON. Did it run faster?
4. Compare the query plans. If UDPATE STATISTICS HIGH produced a different query plan and the query ran faster, then this means that the optimizer made a better choice and the SELECT benefitted from having more data available to the optimizer.

You may have received better results with UPDATE STATISTICS HIGH. However, it might not be feasible for you to take the extra time each day to run HIGH mode on these columns. Instead, you can move back to UPDATE STATISTICS MEDIUM for the columns involved in the query, but this time set the confidence to .99 and adjust the resolution value slightly higher so Lnat the sample size is higher. Then re-run the query and check the query plan to see if it returned the same results as HIGH mode. You can repeat this adjustment-and-test process until the query plan matches the query plan of HIGH mode. Sometimes the number of tables in the query may increase the time required for optimization of the query, since all possible query paths must be examined.

By setting OPTIMIZATION LOW prior to executing a query can reduced the work done by the opitmiser prior to the statement being executed. The disadvantage, is the fact the optimiser is not examining all the possible query paths and therefore might fail to find the optimal query path.

To check to see if OPTIMIZATION LOW is beneficial

	Run and time the query with SET EXPLAIN ON
	SET OPMIZATION LOW and re-time the query. If the query runs slower then OPTIMIZATION should not be used.
	If the query is faster then examine the sqexplain.out file and ensure the two query paths are the same. If theq query paths are the same and the query was faster then OPTIMIZATION was benefinicial

Remember to reset the optimization after the query has complete. This is a common mistake in 4GL and ESQL applications.