Thanks, Dan. I'd have to say that this particular app usually does not update "a row at a time", other than when the user is in an edit form. I'd say almost all of the routines would be updating multiple rows based on some kind of "where" clause. Can't say whether it would be updating 10 rows or 1000 rows, the description below says to use On when "affecting many rows in a table" which would be a subjective matter. Obviously I'm trying to speed up multi-row table updates, not a single-row update, so it looks like Pagelock should be kept On. My guess would be that 90% of the app updates multiple rows, so probably no reason to change from the default On.
Karen -----Original Message----- From: Dan Goldberg <d...@lancecamper.com> To: rbase-l <rbase-l@googlegroups.com> Sent: Tue, May 30, 2017 5:25 pm Subject: RE: [RBASE-L] - Pagelock (corrected text) I run with both pagelock and rowlock on. I tried turning off pagelock once and my users complained it was slow on updating data. I haven’t got time to do the switching per commands. My commands mostly has many update and deletes many rows at a time. Now you can turn it on or off at any time. So you can leave it off and when you have an update or delete command that affects more than one row then you can turn it on and then off after. Razzak had a good article explaining when you would turn on or off the pagelock. http://www.razzak.com/fte/pdf/TipoftheDayDigest_2016.pdf The settings for PAGELOCK are: ON - R:BASE uses page locking or row locking as appropriate. When PAGELOCK is ON and two or more users are updating rows within the same page of data, R:BASE only lets the first user update rows--the other users are locked out until the first user's update has been completed. OFF - R:BASE uses a fast row-locking method where only row locking is applied with no page locking. When PAGELOCK is OFF, you can lock rows of data instead of locking a page of data. You increase multiuser performance when PAGELOCK is OFF. And even more so when STATICDB and FASTLOCK are on. If you know that your application mainly updates or deletes data a row at a time, rather than many rows, set PAGELOCK to OFF for row locking. In this case, R:BASE locks a row, reads the row, makes the change, and then releases the row. Otherwise, set PAGELOCK ON for page locking when you are doing an UPDATE and/or DELETE affecting many rows in a table. Keep in mind that the PAGELOCK setting can be changed dynamically and can be different for different users using the same database. Dan From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com] Sent: Tuesday, May 30, 2017 3:09 PM To: rbase-l@googlegroups.com Subject: Re: [RBASE-L] - Pagelock (corrected text) Ooops, got one word wrong I'm wrestling with the decision of whether to use PAGELOCK OFF in one of my bigger X Enterprise databases. Looking at all the "help" documents it seems like the only reason you would want pagelock On (the default) is if you are updating all the rows in a table, with no "where" clause. As in this help example: -- The UPDATE must alter the values for many rows SET PAGELOCK ON --use page locking UPDATE <tablename> SET <columnname> = value --no WHERE Clause SET PAGELOCK OFF --use row locking So, if I have heavily-used tables with millions of rows and never update without some kind of Where clause, and if the only tables where I might update all rows would be smaller tables, there's no reason to keep the default Pagelock On is there? And I"m guessing that no one knows if there's a threshold where you would want to set it back to On, say if you used a where clause that would update a couple hundred or thousands of records? Or is Pagelock On ignored if there's any kind of where clause? I've never changed the default Rowlocks On setting, so I'm good there. Opinions? Anyone who's recently changed from On to Off and noticed a difference? Thanks everyone! Karen -- 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 torbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- 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 torbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- 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 rbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- 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 rbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
