trying to minimize Write Conflicts in a multi-user database
in [General]
|
Prev: não acho meu orkut
Next: maike yordano pirela vera
From: Paul on 19 Jan 2010 18:54 What a clever use of ListIndex, ListCount and ItemData. Thanks so much for taking the time to create that navigation bar for me, John. The forms in which I'd use it don't allow for any additions, so I won't experiment with that command for the time being. > Also, note that comboboxes are limited to a maximum of 64 K records > (binary K). My largest combo boxes have four fields with no more than a couple of thousand records and they all seem to be working just fine. > By the way after thinking about this, I don't think that the write > conflicts have all that much to do with the number of records you have in > the recordset. Now that you mention it, none of the experts ever told me the two were related, I just imagined that if you loaded multiple records into a form and began editing one, the other records were more likely to be involve in a write conflict than if you only loaded that record you were working on. If write conflicts aren't dependent on the number of records in the recordset, I might just revert back to loading the entire recordset when the form loads, because I'm only dealing with hundreds, not thousands of records. Thanks again for the nice applet with the record navigation bar, John. Paul
From: Paul on 20 Jan 2010 01:11 Great information, Dave. I try to observe good posting rules, and after reading your suggestions I now realize it's best to keep the number of groups ro a necessary minimum. > the first choice in all case is OPTIMISTIC locking Understood. I am using Optimistic locking. > if you're running SQL updates in code against a table that is also loaded > in a form/forms, save the form/forms *before* running the SQL update. This could be a problem, because haven't been doing that. I am running action queries without first saving the record to enforce some of our business rules. Armen and Banana have pointed out that such queries can cause write conflicts. However, Tom Wickerath's page on performance includes a link to a MS Web page that talks about how DAO 3.6 can produce write conflicts. So does that mean it's best to use ADO in place of those update queries? (And is that something that can be done with ADO?) > if you're updating a record in a different subform, save any edits to the > first subform before navigating to the other subform. This is the only way > to avoid write conflicts That's something else I'm not doing. I just assumed that when you leave a subform, the record would be saved automatically. Are you saying that you need to write a line of code to save a record when you click another tab or close the form? > It is never a good idea to have the same table loaded in more than one > editable table simultaneously. My main form has a single field, the PK ProjectID from tblProject, but it's Locked, so it can't be edited. I keep the editable data from tblProject in a subform. I originally had all the data from that table in the Main form, but I ran into a problem which, at the moment, I can't remember what it was, that was solved by moving it into a subform. If users can't edit that single field in the main form, then it shouln't create a write conflict problem. Am I right about that? Thanks for your comments, Paul
From: Tom Wickerath AOS168b AT comcast DOT on 20 Jan 2010 02:42 Hi Marsh, > I missed where you said which version of Jet and/or Ace you > used in the tests?? Access 2003, SP-2. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Marshall Barton" wrote: > Tom Wickerath <AOS168b AT comcast DOT net> wrote: > > >In my last reply, I mentioned monitoring network traffic by looking at the > >packets received data before and after running a query. Earlier this > >afternoon, after I posted that reply, I set up a quick test to check the > >packets data. I wanted to use a large table that anyone else could also have > >access to, so I downloaded Sean Lahman's Baseball database: > > > > http://www.baseball1.com/ > > > >The largest table in this database is the Fielding table, with 158,188 > >records. Still not huge, but at least it's publically available, so that > >others can use it, if interested, to compare results. I created a new BE > >database, disabled Name Autocorrect, and imported the Fielding table. Then I > >copied this database to a shared folder. > > > >~~~~~~~~~~~~~~~~~~~~~~~~ > >Special Note: > > > >I originally was thinking most of the shared folders I have access to are > >located in Bellevue, WA., but it turns out that this particular server is > >located in Kent, WA. I was able to get the building number, and street > >address, using an internal search page. I also got the address for my > >building from the same page, and then plugged these two addresses into > >MapQuest. MapQuest shows a distance of 10.15 miles between my location and > >the location of the file server. So, as much as I'd like to use a LAN only, > >the fact is we are using a WAN, albeit a very fast and rock solid one. > > > >~~~~~~~~~~~~~~~~~~~~~~~~ > > > >I used this aggregate query to produce an intentional table scan: > > > >SELECT PlayerID, YearID, POS, Sum(G) AS GSum > >FROM Fielding > >GROUP BY PlayerID, YearID, POS; > > > >and then I added these two WHERE clauses: > > > >WHERE 1 = 0 > >and > >WHERE False > > > >I had not read Marsh's suggestion of using a wide table at the time I ran > >these tests, otherwise, I would have included all fields from this table. I > >ran each query three times, but started with a different query for each set. > >This is indicated in the spreadsheet with the test number: Set 1 = {1a, 1b, > >1c}, Set 2 = {2a, 2b, 2c} and Set 3 = {3a, 3b, 3c}. I closed the FE > >application in-between sets 1 & 2 and sets 2 & 3. So, hopefully, this would > >have cleared out any records cached in local memory, but I don't know that > >for a fact. > > > >I have uploaded a 30 KB .zip file, which includes the FE.mdb file, a > >Results.xls spreadsheet, and a text file with the URL to the baseball site: > > > > http://www.accessmvp.com/TWickerath/downloads/LargeTableTest.zip > > > >The Excel spreadsheet shows that WHERE conditions of 1=0 and WHERE FALSE > >resulted in an average of 83 and 82 packets transferred (n=3, with Access > >closed after each set of tests), respectively, for queries with these > >criteria. The query without a WHERE clause, run to get an idea of how many > >packets of data would be transferred, resulted in an average of 9531 packets. > >Thus, the two constrained queries resulted in about 0.87% and 0.86% of the > >full table scan. I think it is safe to say, based on these results, that the > >JET ShowPlan indication of a scan for this criteria is not honest. > > > Very nice job Tom. The packet counts appear to conclusively > demonstrate that the query optimizer has definitely been > improved since David's post and my simple test. This is > very good news and we can now forget about playing the > impossible PK criteria game that I brought up earlier. > > I missed where you said which version of Jet and/or Ace you > used in the tests?? > > -- > Marsh > MVP [MS Access]
From: Marshall Barton on 20 Jan 2010 09:14 Tom Wickerath <AOS168b AT comcast DOT net> wrote: >> I missed where you said which version of Jet and/or Ace you >> used in the tests?? > >Access 2003, SP-2. Thanks for all your effort Tom, it was very enlightening. -- Marsh MVP [MS Access]
From: Armen Stein on 20 Jan 2010 16:35
On Tue, 19 Jan 2010 22:11:17 -0800, "Paul" <begone(a)spam.com> wrote: >> if you're updating a record in a different subform, save any edits to the >> first subform before navigating to the other subform. This is the only way >> to avoid write conflicts > >That's something else I'm not doing. I just assumed that when you leave a >subform, the record would be saved automatically. Are you saying that you >need to write a line of code to save a record when you click another tab or >close the form? Paul, your assumption is correct. When your focus moves between a main form and subform, or from one subform to another subform on the same main form, Access implicitly saves the dirty record of the form you are leaving. In other words, within a main form and its subforms, only one record can be dirty at a time. No code extra code is needed for this. However, the explicit save *is* necessary if your focus is not leaving the form, but running an update operation in code on the same records as that form. Armen Stein Microsoft Access MVP www.JStreetTech.com |