Kbase P44946: How to optimize Report Builder reports ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
29/09/2003 |
|
Status: Unverified
GOAL:
How to optimize Report Builder reports ?
FIX:
The following are a guidelines to improve performance and resource usage for Report Builder based reports. These are not solid rules, they will not apply to all situations possible.
1. Use the No Join Reorder option.
The SQL join reorder mechanism Report Builder uses by default is meant to improve performance, but does not always order the joins in the most efficient way. It can end up placing larger table first in a query (based on defined indexes), which can increase memory use and processing time.
Using this option will ensure the join order as defined in the query is used.
2. The number of record reads should be kept at the minimum
In order to achieve this, tables from which the lowest number or records are expected to be returned should be placed first in the query. This would be based on the selection criteria (where clause) involved in the query, and the contents of the tables involved.
3. Tables sorted on should be first in the query.
Doing this will ensure sorting will take place more efficiently. More specifically, more sorting will take place on the database side (if indexes are properly defined) minimizing the need for inefficient client-side sorting. If there are sorts on multiple tables, they should be in the query in order of sort level.
4. Use calculated fields instead of complicated joins.
Doing this correctly decreases the overall complexity of the query, so it can be run more efficiently. Be careful when using aggregate fields in joins; the results of these may not be reliable until the query has run in it's entirety so using the value while the query is running may lead to unexpected results.
This may not always be possible, but it applies in such cases where the join requires multiple fields in one table to map to a single field in the other.
5. If all else fails...
Reduce the report to a single-table report. This can be done by creating an additional "temp" table in the database which is populated from 4GL before the report is run, and emptied afterwards. The report will then be redesigned to use this table.
It's advisable to implement a mechanism that allows for concurrent runs of the report, how this is to be done is left up to the application designer. When running Progress 9 it is also advised that such reporting tables are kept in their own database area.