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<mk:@MSITStore:C:\RBTI\RBGX5E\RBG.CHM::/set_staticdb_2.htm> 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<mk:@MSITStore:C:\RBTI\RBGX5E\RBG.CHM::/update.htm>, INSERT<mk:@MSITStore:C:\RBTI\RBGX5E\RBG.CHM::/insert.htm> and DELETE<mk:@MSITStore:C:\RBTI\RBGX5E\RBG.CHM::/delete.htm> perform much faster with FASTLOCK ON<mk:@MSITStore:C:\RBTI\RBGX5E\RBG.CHM::/set_fastlock_2.htm>. 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<mk:@MSITStore:C:\RBTI\RBGX5E\RBG.CHM::/set_multi_2.htm>, and TRANSACT<mk:@MSITStore:C:\RBTI\RBGX5E\RBG.CHM::/set_transact_2.htm> 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]<mailto:[email protected]>> To: "R:Base List" <[email protected]<mailto:[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]<mailto:[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]<mailto:[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.
