How to increase performance by running UPDATE STATISTICS

How to increase performance in Progress 9.1x running UPDATE STATISTICS
How to increase performance by running UPDATE STATISTICS

If one table has a multi-component index (five fields with 200 records, for example), and the other table has a single index with, for example, 1.6 million records, the time differences in selecting a single field to all fields reports as 5secs:60minutes.

Although there are some known issues with multi component indexes, there was a drastic improvement in performance when UPDATE STATISTICS was run in this case.

A performance increase has been realized in Progress 9.1x and later if UPDATE STATISTICS command is run when an inner join on two tables that have differing component indexes is performed.

The syntax is:

UPDATE STATISTICS for [table_name];

Example:  update statistics for pub.customer;

Table_name specifies a single table to update statistics on. If you do not specify a table_name, the UPDATE STATISTICS statement updates statistics on all tables in the database.

The time required to gather the statistics is highly dependent on the number of tables that are specified and the on the number of rows on those tables.

The optimizer uses the information from UPDATE STATISTICS to make decisions about the best query strategy to use when executing a particular SQL-92 statement.

Until a user, an application, or a SQL-92 script issues an UPDATE STATISTICS statement, the optimizer bases query strategies on values it derives from various defaults. The values might not lead to the best performance, so the database administrator should issue an UPDATE STATISTICS operation periodically.

References to other Documentation:

OpenEdge Data Management:  SQL Development:  Optimizing Query Performance

The Progress® RDBMS SQL92 Optimizer and the Optimizing SQL92 Queries with Index Statistics white papers located on Progress Communities.  See article:   How to access the Progress Community site?.  Once on Communities, search for SQL 92 Optimization and sql index statistics to locate these two white papers. Progress Article(s):

 UPDATE STATISTICS causes performance degradation