Kbase 20952: How to increase performance in Progress 9.1x running UPDATE STATISTICS
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/10/2008 |
|
Status: Verified
GOAL:
How to increase performance in Progress 9.1x running UPDATE STATISTICS
FACT(s) (Environment):
Progress 9.1x
All Supported Operating Systems
FIX:
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 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 [table_name];
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.