Thank you, Mark.
BTW: Razzak and crew pulled the various multi-user help documents
together into the 01/04/2021 "Multi-User Guide", found in the "From the
Edge" collection. The sections on optimization are especially
appreciated.
Best, Bruce
Bruce A. Chitiea | SafeSectors, Inc.
112 Harvard Ave #272 | Claremont CA 91711-4716 | USA
[email protected] | +011 (909) 238-9012 c | +011 (909) 912-8678 f
------ Original Message ------
From: "Mark Lindner" <[email protected]>
To: "[email protected]" <[email protected]>
Sent: 4/5/2021 6:09:13 PM
Subject: RE: [RBASE-L] - PAGELOCK - Tweaking Performance Question
I can at least answer some of your questions.
From the Help files
Technically, the efficient and fastest method for updating data in
multi-user environment is to SET STATICDB ON, SET ROWLOCKS ON, SET
FASTLOCK ON, and SET PAGELOCK OFF. This particular combination will
result in the fewest contentions between users.
Database settings must all be the same for MULTI, either on or off.
Multi allows for multiple users to access the database. If they do
not match the attempts to connect after the first will not be
successful. The notification will depend on your error messages
settings. If you have messages off, it simply will not connect, and
your application most likely will give you an R prompt.
Static on/off controls whether or not changes can be made to the
structure while the database is open. All connections after the first
must match that setting. I believe the theory is that it is faster if
it does not have to check for changes as it is working. It would not
make sense to allow some users to make structural changes while the
others are not checking. If one can make a change, all must be set to
detect it. If no one can, then checking for change is not needed.
As to FASTLOCK, all I know is what is in the help file
One of the areas of most contention when updating or deleting data is
the table lock that must be placed so that R:BASE knows the table is in
use and the table structure cannot be modified. Each user modifying
data places this lock, and uses the same lock location. With STATICDB
set ON, no structure changes are possible on permanent database tables,
thus, there is no need for a table lock preventing a user from
modifying the structure of a table. Since the lock is not needed, the
FASTLOCK setting was added in R:BASE to allow users to turn this table
lock off. Commands such as UPDATE, INSERT and DELETE perform much
faster with FASTLOCK ON.
The FASTLOCK setting must match for all users of a database and must be
set before a database is connected. Users cannot connect a database
unless their FASTLOCK setting matches the setting of the open database.
FASTLOCK joins STATICDB, MULTI, and TRANSACT as settings that must
match for all users connected to a database. FASTLOCK cannot be set ON
unless STATICDB is ON. If you SET FASTLOCK ON and connect the database,
but STATICDB is OFF, after connecting to the database FASTLOCK is set
OFF.
You may find the explanations in the online help useful. From the R:
prompt type help pagelock, help fastlock for a better explanation.
Mark Lindner
Lindner & Associates
PO Box 327
Randolph MA 02368
781 247 1100
From:[email protected] <[email protected]> On Behalf Of
Bruce Chitiea
Sent: Monday, April 5, 2021 6:47 PM
To:[email protected]
Subject: Re: [RBASE-L] - PAGELOCK - Tweaking Performance Question
Doug: Happy Easter, Indeed.
I don't have the answer you seek, but, I'm working in the same arena,
looking to optimize the SET stack in a .DAT startup file.
For starters, the most up-to-date SETTINGS reference available appears
to be Section 1.1 "Multi-User Concurrency Settings" found in the R:BASE
Multi-User Guide: (2021-01-04) in the From the Edge collection. In my
estimation, the document is close, and getting closer to the definitive
guide you and I both seek. My practice is to explicitly declare
settings in code, rather than to rely on documented, though uncoded,
default values.
To encourage progress towards development of that definitive guide, a
few questions:
(1) SET MULTI [ ON | OFF ] I have read somewhere (but the current
document does not confirm) that if the first connecting workstation has
SET MULTI ON, workstations attempting to connect without SET MULTI ON
are prevented from connecting to the database.
(a) Is this true, and, alternately,
(b) how is an existing single user connection affected if a second
station attempts to connect with SET MULTI ON?
(c) What feedback, if any, does either user receive?
(2) SET STATICDB [ ON | OFF ] The document reads: "A user who first
connects ... with STATICDB set to on ..." forces all subsequent users
attempting to connect to have STATICDB ON.
(a) How is an existing single-user connection affected if a second
station attempts to connect with SET STATICDB ON?
(b) What feedback, if any, does either user receive?
(3) SET FASTLOCK [ ON | OFF ] The document reads: "... FASTLOCK
requires all users to be connected with the same setting."
(a) How is an existing single-user connection affected if a second
station attempts to connect with SET FASTLOCK ON?
(b) What feedback, if any, does either user receive?
(4) SET PAGELOCK [ ON | OFF ] The document records that the default
value is ON, that the value may be different for different users, and
that PAGELOCK may be set dynamically within application code. The
document also reads: "ON - R:BASE uses page locking or row locking as
appropriate."
(a) What is the practical meaning of "as appropriate."?
(b) IF the default value of ON (implicitly) significantly degrades
performance, would not OFF be the better choice, and, as the document
suggests by example, allow the user to toggle it ON/OFF while
optimizing the application?
(c) Is there any particular setting below which PAGELOCK must appear?
(d) Is there any particular setting above which PAGELOCK must appear?
(d) Need PAGELOCK be explicitly set in the .DAT file BEFORE database
connection, or may this be left for the application?
(5) SET ROWLOCKS [ ON | OFF ] The document is rather terse. The
document reads: "When ROWLOCKS is set off, R:BASE sets table locks
during each UPDATE ... "
(a) This appears to override the effect of STATICDB ON, suggesting that
ROWLOCKS may be dynamically set within application code, and at any
given moment have a different value for different users.
(b) Can, in fact, ROWLOCKS be set dynamically within an application?
(c) Is there any particular setting below which ROWLOCKS must appear?
(d) Is there any particular setting above which ROWLOCKS must appear?
(e) Need ROWLOCKS be explicitly set in the .DAT file BEFORE database
connection, or may this be left for the application?
(6) [ INTERVAL | WAIT ] Document is pretty straightforward. But the
document reads: "When the command runs as part of a command file,
however, and the waiting period expires, R:BASE ignores the command and
goes on to the next command."
(a) What if this "breaks" a stream of processing in a most unseemly and
barely recoverable fashion? Is this the Number One Argument In Favor Of
targeted Transaction Processing?
(b) Are there any activity monitoring tools available which would
enable an admin to determine whether Transaction Processing or
adjustment of the WAIT and INTERVAL values might be the best defense?
(c) Can WAIT and INTERVAL be dynamically set within an application
setting?
(d) Can WAIT and INTERVAL values differ for different users?
(e) Is there any particular setting below which [ INTERVAL | WAIT ]
must appear?
(e) Need INTERVAL | WAIT be explicitly set in the .DAT file BEFORE
database connection, or may this be left for the application?
(7) VERIFY [ ROW | COLUMN ]
(a) Does this setting need to be the same for all users connecting to
the database?
(b) If so, what happens when a user attempts a connection with the
converse value? What notification is given?
(c) Is there any reason whatsoever to set VERIFY values dynamically
within code?
(d) Is there any particular setting below which VERIFY must appear?
(e) Is there any particular setting above which VERIFY must appear?
(f) Need VERIFY be explicitly set in the .DAT file BEFORE database
connection, or may this be left for the application?
------ Original Message ------
From: "Doug Hamilton" <[email protected]>
To: "R:Base List" <[email protected]>
Sent: 4/4/2021 9:02:45 AM
Subject: [RBASE-L] - PAGELOCK - Tweaking Performance Question
I'm trying to tweak performance on an R:Base X.5E order processing
system (> 2Gb ) with ~10 users.
We've been running with PAGELOCK ON; I'd like to set it OFF since most
of the SQL commands have WHERE clauses or are DECLARE CURSOR.
I want to update the RBase.DAT file to set PAGELOCK OFF with these SET
commands:
SET MULTI ON
SET STATICDB ON
SET VERIFY COLUMN
SET FASTLOCK ON
SET ROWLOCKS ON
SET PAGELOCK OFF
SET WAIT 4
SET INTERVAL 5
SET REFRESH 0
So I have some questions.
1) Is the order of the SET commands critical? I originally had
ROWLOCKS before FASTLOCk and R:Base was freezing up. Another
developer suggested the above sequence, putting FASTLOCK before
ROWLOCKS.
2) PAGELOCK replaced some of the function of QUALCOLS starting in
version X; What should be the correct QUALCOLS value if I use the
above SETs? (Client does not [currently] SATTACH to other data
sources)
3) If a row of data is being updated, does that slow down read-only
performance, especially if PAGELOCK is on? e.g. SEL COUNT(Column)
FROM table
I welcome any other suggestions.
I would test the above but client has been very patient. I don't want
to cause any more anomalies than necessary.
Thanks and Happy Easter
Doug
-- This email has been checked for viruses by Avast antivirus
software.
https://www.avast.com/antivirus
-- For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
--- You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/87695ee0-de9b-10ec-3bbb-4e51fbda7f01%40wi.rr.com
<https://groups.google.com/d/msgid/rbase-l/87695ee0-de9b-10ec-3bbb-4e51fbda7f01%40wi.rr.com>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
<http://www.rbase.com/support/usersgroup_guidelines.php>
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/emd0022201-b645-4e78-99bb-6b5f603df7c6%40pathfinder
<https://groups.google.com/d/msgid/rbase-l/emd0022201-b645-4e78-99bb-6b5f603df7c6%40pathfinder?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/MN2PR10MB43178DB918689ADD256A550A8F769%40MN2PR10MB4317.namprd10.prod.outlook.com
<https://groups.google.com/d/msgid/rbase-l/MN2PR10MB43178DB918689ADD256A550A8F769%40MN2PR10MB4317.namprd10.prod.outlook.com?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/em7a48a94c-283e-48ad-b377-85427890d29f%40pathfinder.
