DOCUMENT #680
=======================================================================
FINE TUNING R:BASE
=======================================================================
Product: R:BASE Version : 3.1C & Higher
=======================================================================
Area : PERFORMANCE Category: CONFIGURATION
=======================================================================
Of the many enhancements added to R:BASE, one is the ability for you,
the developer, to fine tune R:BASE itself. Most often, R:BASE is able
to make the right decisions about the best way to process commands;
but sometimes, you know your data better and could pick a more
efficient method if you were only given the opportunity. R:BASE
now gives you the opportunity to tune performance. The following
can be used to increase application performance:
<> MANOPT setting and query optimizer control
<> MICRORIM_MAXF2BLK, MICRORIM_MAXF3BLK and MICRORIM_REFRESH
variables
<> SET SORT parameter -H, -Z, and -K startup switches
MANOPT,#TABLEORDER
==================
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, i.e. 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.
Here's 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=1 *(viewing 1 row is sufficient
to see the table order, and
is fast)
WRITE .#TABLEORDER
employee,transmas,transdet.transid,customer.custid,product.model
| | | | |
table 1 | | | |
no index used | | | |
table 2 | | table 5
no index used | | uses index column
table 3, uses | model to link with
index column transid | transdetail
to link with transmaster |
table 4, uses
index column custid 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. Removing or adding
indexes on linking columns will change the default table order. With
Prodview, the algorithm determined it was best to start with the
Employee table and link to the Transmaster table (note that
#TABLEORDER displays only the first 8 characters of the table or
column name only). The column empid links the two tables, but it
is not indexed in either table. R:BASE then takes the result of
that join and adds in the Transdetail table, but now there is an
indexed column it can use. It takes the rows from the Employee/
Transmaster join and performs indexed lookups on the Transdetail
table to find matching rows based on transid. The Customer and
Product tables are similarly linked in. 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(0,0,.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. For example, the view, Prodview:
View: prodview
SELECT
#T1.transdate,#T2.*,#T3.*,#T4.empid,#T4.empfname,#T4.emplname,
#T5.prodname FROM transmaster #T1,transdetail #T2,customer #T3,
employee #T4,product #T5
WHERE ((( #T1.transid = #T2.transid ) AND ( #T1.custid = #T3.custid )
AND ( #T1.empid = #T4.empid ) AND ( #T2.model = #T5.model )))
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's what #TABLEORDER shows when we have MANOPT ON and select from
Prodview:
transmas,transdet.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. The article
"Optimizing Application Code for Speed" in the May/June 1992 Exchange
Technical Journal has examples of how table order affects the
performance of a SELECT.
In most instances, R:BASE chooses the most efficient way to join your
tables; but you can use the MANOPT setting to experiment and see if a
different table order is better.
In addition, in R:BASE 4.0A, the query optimizer has been enhanced to
perform a query based on a "first row fastest" or "overall result set
fastest" algorithm. Certain commands, like Browse/edit, always use
the "first row fastest" algorithm for a quick data display. From the
R> prompt, the SET LINES setting forces R:BASE to use one algorithm or
the other. SET LINES 0 uses the "overall result set fastest"
algorithm; any other setting for LINES uses the "first row fastest"
algorithm.
When printing a report based on a view (a stored SELECT), set R:BASE
to use the "overall result set fastest" algorithm if you are printing
to a file or a spooled printer. Use the "first row fastest" algorithm
if you are printing to a non-spooled printer.
SET SORT, SHOW SORT
===================
SET SORT ON is available in RBASE 3.x and 4.0. Other SET SORT
parameters are available only in R:BASE 4.0A in the 386/486 version.
If there is not enough memory space to do a sort, R:BASE creates
temporary sort files (.$$$). The SET SORT ON parameter optimizes
disk space usage. This is helpful if you get the message "Unable
to create sort file. Check for full disk." Versions of R:BASE prior
to 4.0A do not have the option to increase the amount of memory used
for sorting, it is limited to 64K.
With 4.0A 386/486, you have the new options SET SORT MAX, and SET
SORT MIN . R:BASE runs in extended memory and as such has a large
pool of memory available for sorting (as opposed to conventional
memory, with only 640 K total memory, sort space was limited to 64K).
By default, with 4.0A, sorting uses all available extended memory.
You can use the SET SORT MAX and the SET SORT MIN parameters to
allocate memory for sorting; setting maximum and minimum limits,
respectively.
Before using either of these parameters, use SHOW SORT LAST. This
shows the amount of memory (in Kb) that R:BASE calculated would be
needed to sort in memory (no disk swapping) the data from the last
executed command. This is a calculated amount, it is not the actual
amount of memory used. Execute your large sorts or print your reports
and then do SHOW SORT LAST. You'll see how much memory to allocate
with SET SORT. Make sure your SET SORT MAX is at least the size shown
by SHOW SORT LAST. Use SHOW SORT MAX, SHOW SORT MIN to display the
current settings. The default value for both is 0, R:BASE uses all
available extended memory.
-H Startup Option
==================
The -H startup option is available in the 286 version of R:BASE only.
By default, R:BASE uses 300 memory handles in the 286 version.
There are situations when this is not enough. Two of the most common
are reports with lookups (each lookup takes on average 6 memory
handles) and rules (each rule takes on average 6 memory handles).
If you get the error message "Out of dynamic memory handles. Check
application complexity.", you may want to try starting R:BASE with
the -H option. This lets you increase the number of memory handles
R:BASE can use. For example, RBASE -H320, starts R:BASE and allows
use of 320 memory handles.
The number of additional memory handles is limited by the size of
the memory area where the memory handle entries are allocated. You
cannot allocate more than 460 memory handles. If you need more than
this, you should examine your application and use of features
requiring the memory handles.
-K Startup Option
=================
The -K startup option is available in the 386/486 version of 4.0A
only.
Another 4.0A parameter you can use to tune R:BASE is the -K startup
option. In prior versions of R:BASE, R:BASE would grab 64K of memory
for its data area (processing expressions, while loops, sorts etc.),
then when it needed more memory it would grab another 64K chunk.
With 4.0A, the default has changed to 128K; but the -K option gives
you the opportunity to increase that amount. You can tell R:BASE to
grab 1024K (1 Mb) of memory right off the bat for processing, and
memory continues to be allocated in 1 Mb pieces. For example, RBASE
-K1024, allocates memory in 1 Mb chunks.
Use this option ONLY if you consistently have "Out of dynamic space"
errors or if your OS/2 SWAP file continually grows.
-Z Startup Option
=================
The -Z startup option is available in the 386/486 version of R:BASE
only.
By default, R:BASE (386/486) does not use any conventional memory
for processing. When it needs additional memory, it allocates it
from extended memory only. All available conventional memory can
thus be used by other programs for ZIPping. This conventional memory
is also used by CodeLock and Gateway (Import/export). If R:BASE runs
out of extended memory to allocate, you can tell R:BASE to use
conventional memory, if needed, by using the -Z startup option.
With the -Z option you specify an amount of memory (in Kb) to
reserve, i.e. you specify the amount of conventional memory R:BASE
will not attempt to use.
For example, RBASE -Z10 allows R:BASE to use all but 10 Kb of
available conventional memory. RBASE -Z200 uses all but 200 Kb
of conventional memory.
MICRORIM_MAXF2BLK, MICRORIM_MAXF3BLK
====================================
When RBASE 4.0 (386/486) needs to access data from the disk, it must
switch from protected mode back to real mode. This can degrade
performance because of the amount of switching back and forth that
R:BASE must do. By default, R:BASE uses six buffers for file 2
(data) and six buffers for file 3 (indexes). The file 2 buffers are
5120 bytes each in the 286 version and 8192 bytes each in the 386/486
version. The file 3 buffers are 512 bytes each regardless of version.
In single-user mode you can increase the number of default buffers by
using the system variables MICRORIM_MAXF2BLK, MICRORIM_ MAXF3BLK.
SET VAR MICRORIM_MAXF2BLK=n
SET VAR MICRORIM_MAXF3BLK=n
"n" is the number of buffers to allocate. The maximum number of
buffers is 500. Don't forget that each additional buffer takes memory,
and also a memory handle in the 286 version. The number of buffers
you can set is limited by your available memory. You must set these
variables before you connect a database.
Setting the buffers higher allows R:BASE to read from disk and hold
in memory larger amount of data. For example, setting
MICRORIM_MAXF2BLK to 20 means you can hold 20*8192 = 163,840 bytes
of data from file 2 in memory in the 386/486 version and 20*5120 =
102,400 bytes of data in the 286 version. Setting MICRORIM_MAXF3BLK
to 30 means you can have 30*512 = 15360 bytes of index (file 3)
information in memory at one time.
Increasing your file 2 buffers helps multi-table joins, particularly
if one of the smaller tables can be held entirely in memory.
Increasing your file 3 buffers also helps because indexes are used
in joining the tables. Sorting, single sequential passes through the
data and index building with CREATE INDEX are not accelerated by
increasing the buffers.
Multi-user mode ignores these variables because of concurrent access
to data by various users. A large amount of data held in memory by
one user is not appropriately updated by other users.
MICRORIM_REFRESH
================
The variable, MICRORIM_REFRESH, new in 4.0A, must be used with caution.
In a multi-user environment, it specifies the amount of time before
data in local memory buffers is re-read from disk. Normally, all
data is refreshed after every command. If you are doing repetitive
selections (reads) from the same table(s) in a multi-user system,
you can see dramatic performance improvements by setting this variable.
For example, SET VAR MICRORIM_REFRESH=30, to flush the local buffers
and re-read information from disk every 30 seconds. To reset to the
default, simply CLEAR VAR MICRORIM_REFRESH.
This feature should only be used in situations where you are
performing read-only operations on a multi-user database and are
not concerned with viewing updates made by other users immediately.
Do not use this feature in situations where the database could be
updated by multiple people.
Which Ones To Use
=================
The options detailed above can help you reach maximum performance
with R:BASE. But, since each application and database is a unique
set, only a trial and error process will identify which options help
in your situation. Don't forget that different hardware configurations
and available memory also affect performance. Experiment with the
above options and see which ones improve performance for your appli-
cation. You, the developer, now have the opportunity the set R:BASE
internal parameters to gain optimum performance for each application.