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.
--
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/emd0022201-b645-4e78-99bb-6b5f603df7c6%40pathfinder.
