Tuesday, February 26, 2013
Tip of the Day: Optimizing R:BASE Multi-User Environment
Product: R:BASE eXtreme 9.5 (32/64)
Build..: 9.5.2.20128 or higher www.rupdates.com
Section: Multi-User Environment Considerations
The ability to fine tune R:BASE itself is one of the many enhancements added
over the years. Most often, R:BASE is able to make the right decisions about
the best way to process commands.
Generally, you know your database and application code better than anyone
else. However, you should always take advantage of the latest enhancements
and syntax, and would pick a more efficient method if given the opportunity.
Did you know that there are three settings that can improve performance
in a multi-user environment?
. STATICDB
. FASTLOCK
. QUALCOLS
STATICDB limits structural changes to the database, and activates a read-only
schema mode. When STATICDB is set to ON, permanent changes cannot be made to
the database structure (table and column names, for example). New tables can
be created, but they are temporary and visible only to the user who created
them. By restricting changes to the database structure, R:BASE does not
continually re-read the structure to check for changes. Hence, multi-user
applications run faster. All users connected to a multi-user database must
have the same setting for STATICDB. The default is OFF.
In a multi-user environment, a user who first connects to a database with
STATICDB set to ON engages that database to operate in a read-only
schema mode, whereby any user must have their STATICDB setting ON in order
to connect to that database.
SET FASTLOCK ON for faster multi-user performance while modifying data. With
FASTLOCK ON, R:BASE does not place a table lock on the table, allowing for
greater throughput. A table lock is only needed to prevent structure changes.
FASTLOCK can only be set to ON when STATICDB is set to ON, and both FASTLOCK
and STATICDB must be set ON before the database is connected.
QUALCOLS setting is used to turn page locking OFF and use row locking instead
and the ROWLOCKS setting is no longer used to turn row locking ON or OFF.
If you know that your application mainly updates or deletes data a row at a
time, rather than many rows, SET QUALCOLS 2 for row locking.
Use 10 for page locking or row locking as appropriate. Use 2 for row locking
only, not page locking. In this case, R:BASE locks a row, reads the row, makes
the change, and then releases the row.
Keep in mind that the QUALCOLS setting can be changed dynamically and can be
different for different users using the same database. In most cases the SET
QUALCOLS 2 is a good combination. However, use SET QUALCOLS 10 for page
locking when you are doing an UPDATE and/or DELETE affecting many rows in a
table.
Here is a routine that you can use to automate the process of CONNECTing the
database in a network environment with SET STATICDB ON, SET FASTLOCK ON, and
SET QUALCOLS 2.
-- Example
-- Start here ..
-- MyApp.DAT Startup Application File
-- Start Fresh
CLEAR ALL VARIABLES
LABEL StartFresh
DISCONNECT
SET QUOTES=NULL
SET QUOTES='
SET DELIMIT=NULL
SET DELIMIT=','
SET LINEEND=NULL
SET LINEEND='^'
SET SEMI=NULL
SET SEMI=';'
SET PLUS=NULL
SET PLUS='+'
SET SINGLE=NULL
SET SINGLE='_'
SET MANY=NULL
SET MANY='%'
SET IDQUOTES=NULL
SET IDQUOTES='`'
SET CURRENCY '$' PREF 2 B
DISCONNECT
SET STATICDB OFF
SET ROWLOCKS ON
SET FASTLOCK OFF
SET TIMEOUT 120
SET FEEDBACK OFF
LABEL Start
DISCONNECT
SET STATICDB ON
SET FASTLOCK ON
SET QUALCOLS 2
SET MESSAGES OFF
SET ERROR MESSAGES OFF
SET ERROR MESSAGE 2495 OFF
CONNECT dbname IDENTIFIED BY ownername
SET ERROR MESSAGE 2495 ON
SET MESSAGES ON
SET ERROR MESSAGES ON
-- Check the availability of database
IF SQLCODE = -7 THEN
CLS
PAUSE 2 USING 'Unable to Connect the Database.' +
CAPTION ' Your Application Caption Here ...' +
ICON WARNING +
BUTTON 'Press any key to continue ...' +
OPTION MESSAGE_FONT_NAME Tahoma +
|MESSAGE_FONT_COLOR RED +
|MESSAGE_FONT_SIZE 11 +
|THEMENAME R:BASE Longhorn
CLOSEWINDOW
EXIT
ENDIF
-- Enforce Database Default Settings
SET QUOTES='
SET DELIMIT=','
SET LINEEND='^'
SET SEMI=';'
SET PLUS='+'
SET SINGLE='_'
SET MANY='%'
SET IDQUOTES='`'
SET CURRENCY '$' PREF 2 B
SET NULL ' '
SET DATE FORMAT MM/DD/YYYY
SET DATE SEQUENCE MMDDYY
SET DATE YEAR 30
SET DATE CENTURY 19
CLS
EDIT USING ApplicationMainMenu
RETURN
-- End here ...
Notes:
01. Correct the appropriate lines accordingly if you wish to use STATICDB,
FASTLOCK, and QUALCOLS settings.
02. Verify the CHARacter SETtings related to your database.
03. Verify the DATE and TIME settings related to your database.
04. Re-visit the routine that closes the application to either EXIT or go
back to the Database Explorer or R> prompt.
05. Create for yourself a startup file with SET STATICDB OFF prior to
CONNecting
the database when you are in "development mode" and making structural
changes, when no one else is connected to the same database.
-- Example
DISCONNECT
SET STATICDB OFF
CONNECT dbname IDENTIFIED BY ownername
RETURN
The options and example 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 identifies which options help in your situation. Do
not forget that different operating systems, hardware configurations,
available
memory, band-width, system security, and installed anti-virus
settings can also
affect performance. Experiment with the known options and see which
ones improve
performance for your application. You, the developer of your application, have
the opportunity to set R:BASE internal parameters to gain optimum performance
for each application.
Stay productive, my friends!
Very Best R:egards,
Razzak.
www.rbase.com
www.facebook.com/rbase
www.RazzakMemon.com
