Use the MANOPT setting in a multi-table select or with a view to explicitly specify the table order for R:BASE to use when joining tables. Before experimenting with changing the table order, you need to know the default table order, e.g. the table order R:BASE has chosen using its internal optimizing algorithm. To see the table order R:BASE used when joining the tables with SELECT, use the #TABLEORDER variable. You must SET DEBUG ON to use MANOPT and #TABLEORDER.
Here is an example using Prodview from the CONCOMP sample database. Prodview is a five table view. The #TABLEORDER variable shows the table join order and the applicable indexed columns.
SET MANOPT OFF -- must be off to use R:BASE's default optimizing algorithm
SET DEBUG ON -- required to access #TABLEORDER
SELECT * FROM prodview WHERE LIMIT=l
--viewing 1 row is sufficient to see the table order, and is fast)
WRITE .#TABLEORDER
customer, transmaster.custid, employee, product, transdetail.transid
Results:
table 1 (customer) - no index used
table 2 (transmaster.custid) - used index column custid to link with customer
table 3 (employee) - no index used
table 4 (product) - no index used
table 5 (transdetail.transid) - used index column transid to link with transmaster
R:BASE looks at the size of the tables (number of rows and columns) and indexes when using the optimizing algorithm to join tables. Removing or adding indexes on linking columns changes the default table order. With Prodview, the algorithm determined it was best to start with the Customer table and link to the Transmaster table. R:BASE then takes the result of that join and adds in the Employee and Product tables. Finally, the data in the Transdetail table is looked up using the transid index. Now that the default table order R:BASE used is known, the MANOPT setting is used to change this order and determine performance. The following piece of code checks for performance differences based on the MANOPT setting.
SET DEBUG ON
SET MANOPT ON --or OFF
SET VAR vStart=.#TIME
OUTPUT DUMMY.DAT
SELECT * FROM prodview
OUTPUT SCREEN
SET VAR vEnd = .#TIME
SET VAR vDiff = (.vEnd-.vStart)
SET VAR vTime = (RTIME(O,O, .vDiff))
SHOW MANOPT
WRITE .#TABLEORDER
WRITE .vTime
When working with MANOPT, the important part of the view definition or SELECT command is the FROM clause. The table order in the FROM clause of the view definition is transmaster, transdetail, customer, employee, product. With MANOPT ON, R:BASE uses the table order as specified in the FROM clause to join the tables. Notice that the table order in the FROM clause is different from the default table order selected by the query optimizer.
Here is what #TABLEORDER shows with MANOPT ON and selecting from Prodview:
transmaster,transdetail.transid,customer.custid, + employee, product. model
This is different from the order selected by R:BASE's query optimizer. R:BASE now uses tables in the order specified in the SELECT's FROM clause, incorporating applicable indexed columns. By changing the order of the tables in the FROM clause, you can change the order R:BASE joins the tables for possible improved performance.
In most instances, R:BASE chooses the most efficient way to join your tables; however, you can use the MANOPT setting to experiment and see if a different table order is better.
