Running a rbase database connection over wireless is troublesome as the wifi is not a perfect connection. You can use remote desktop to the server so none of the database connections are running over the wifi.
Dan Goldberg From: [email protected] <[email protected]> On Behalf Of Doug Hamilton Sent: Tuesday, April 6, 2021 9:29 AM To: [email protected] Subject: Re: [RBASE-L] - PAGELOCK - Tweaking Performance Question Thanks for your comprehensive research, Bruce - the "other developer" I referenced in my original email. Some answers in-line. Thanks for your response Mark - I had tried PAGELOCK OFF, see my comments below. Questions for the list: Universal question for all the settings: which ones must set before connecting the db (MULTI is one) and which can be set after connection (PAGELOCK is one). Our big issue on the system is unpredictable performance: response is sporadic - sometimes forms response and printing is virtually instantaneous and a few minutes later we can wait 15 - 30 seconds for lookup in a form. There are also instances of random data corruption - rows not getting updated, data getting changed, occasional lines not getting written to TXT files. The location is an industrial parts packaging facility. There are 6 wired connections and 3 wifi computers on carts in the shipping department. All machines are new Win 10 computers. The carts have bar code scanners and label printers and local installations of R:Base so the RBASE.EXE doesn't have to come over the wireless. All startup.DAT files are read from a \STARTUP folder on the server. About 5 of the machines are heavy users - including the carts. As of this past Sunday, all were updated to RB 10.5.3.20304. They had been 10.5.3.11022. Coincidentally, the consensus of users is that responses are slower after the upgrade. (Upgrade was the only system change, no changes to DAT or CFG files.) In the RBASE.DAT file before connecting, I have among other things: SET MULTI ON SET STATICDB ON SET FASTLOCK ON SET ROWLOCKS ON --SET PAGELOCK OFF ... CONNECT ... Prior to Sunday's update, I thought PAGELOCK OFF would speed things up but it appeared to cause R:Base to lock up; I took it out. Is the sequence of the SETs critical; does ROWLOCKS need to follow FASTLOCK? RB help states: "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." implying ROWLOCKS precedes FASTLOCK. Some thoughts I'm considering: Don't run Windows on the carts - run Windows on virtual machines on the server and remote desktop to them from the carts. Is wifi a source of the noted issues? Read RBENGINEX5E.CFG from the server using -O option to ensure all machines have the same config. (DAT files already are read from the server). Idea Box: set NAME to something unique, e.g. X5E210407 so that R>SHOW NAME confirms the intended CFG is being read. We've also rebooted all machines and network gear but not the server. Any corroboration or suggestions are welcome. Client has been more than patient but we are both frustrated. TIA, Doug On 4/5/2021 5:47 PM, Bruce Chitiea wrote: 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? Can't connect to the db message.. (c) What feedback, if any, does either user receive? Err msg: The database is currently unavailable -- it is probably open with MULTI set off. (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? Similar to SET MULTI (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. I believe STATICDB prevents changes to the db structure/data dictionary; ROWLOCKS OFF locks the whole table from data changes by other users, ROWLOCK ON locks only the row of data for that user - other users can change data in other rows. (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 ________________________________ [Avast logo]<https://www.avast.com/antivirus> This email has been checked for viruses by Avast antivirus software. www.avast.com<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/fd9847db-60f8-cc8b-bc64-d30ac0c2aaee%40wi.rr.com<https://groups.google.com/d/msgid/rbase-l/fd9847db-60f8-cc8b-bc64-d30ac0c2aaee%40wi.rr.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/2E4830CEC16615458329D3D83342F8D0014E855EB2%40EXCHANGE10.
