Consultor Eletrônico

Report Builder 8.3A - Improve performance with new Option


Report Builder - Improve performance with new Option

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.


KB- 18172

8/98