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
      <> SET SORT parameter -H, -Z, and -K startup switches 
     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)
          |          |               |               |               |         
        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 MANOPT ON  *(or OFF)
       SET VAR vstart=.#TIME
       SELECT * FROM prodview
       OUTPUT SCREEN    
       SET VAR vend = .#TIME
       SET VAR vdiff = (.vend-.vstart)
       SET VAR vtime = (RTIME(0,0,.vdiff))
       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                                                            
     #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 
     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" 
     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 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, 
     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 
     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 
     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.
     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.  
     "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.  
     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.