Kbase P81421: Report Builder. Improve performance
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
14/07/2009 |
|
Status: Unverified
GOAL:
Report Builder. Improve performance
GOAL:
How to improve perfromances with Report Builder?
GOAL:
Improving performances with Report Builder
GOAL:
Undocumented RB startup parameter to improve performance
FIX:
The SQL Engine, which processes the SQL in a report, will sometimes change the order in which the tables are specified in the From Clause of the Select.
This is done in an attempt to optimize the SQL Select statement,however, sometimes it has the opposite effect.
For example, tableA has 30,000 records and tableB has 500,000 records. The following Select statement was written with tableA as the master table.
SELECT tableA.name, tableB.order-num
FROM tableA, tableB
WHERE tableA.cust-num = tableB.cust-num
The SQL engine may reverse the tables so that tableB is processed first. For example,
SELECT tableA.name, tableB.order-num
FROM tableB, tableA
The SQL Engine will look thru every record in the first table and try to match it to a record in the second table.
Since there are more records in tableB than in tableA, the report will take longer when tableB is first.
***** An option has been added to Report Builder that allows the user to specify that they do not want the order of the tables changed. If the menu item Database->NO JOIN REORDER is checked, the SQL Engine will NOT change the order of the tables. *****
If this Option is selected and the report is re-saved then it is saved with the report.
This option may help increase performance on reports that contain SQL using the implicit join syntax.
This option will have no effect if it is used with the -rbexpjoins startup parameter.
( -rbexpjoins tells Report Builder to use explicit join syntax for inner joins )
See the SQL Guide and Reference for a discussion of the differences between implicit and explicit join syntax.
Some people may opt for using a startup parameter instead of this option in Report Builder. This undocumented startup parameter is -noreorder .
The -noreorder startup parameter is passed to the SQL engine. It overrides the menu option. If you start RB with -noreorder, the setting of the No Join Reorder menu option doesn't matter; the report will be run without the join optimization.
-noreorder does not affect what is stored in the report. The current setting of the menu option is what is stored in the report when you save it. So starting RB with -noreorder and saving a report without No Join Reorder checked will not turn on No Join reorder in the saved report.
This Parameter can be run from the rbstart.pf or startup.pf files as well as in the line where you initally start your Progress session. For more information about these files please read Report Builder Admin Guide Section A.2.1 Using Parameters When Starting Report Builder.