trying to minimize Write Conflicts in a multi-user database
in [General]
|
Prev: não acho meu orkut
Next: maike yordano pirela vera
From: Armen Stein on 17 Jan 2010 09:24 On Sat, 16 Jan 2010 23:27:56 -0800, "Paul" <begone(a)spam.com> wrote: >One approach I've already tried is to have the Timer event of the form save >the form's record every few minutes to prevent against people leaving an >edited record unsaved for long periods of time. The issue with that approach is that when the user presses the Esc key to undo their changes, sometimes it won't work. Not confidence-inspiring. Over hundreds of apps we've never auto-saved on a timer. However, we *have* had situations where we've forced a save to the current record before running other update queries on the same table. Armen Stein Microsoft Access MVP www.JStreetTech.com
From: Banana on 17 Jan 2010 10:35 Paul wrote: > Based on what you've said, and what I read in that Microsoft article you > referenced, it would probably be a good idea to replace some of my action > queries was DAO code, because I am performing action queries on continuous > (tabular) view subforms. Sure. It's a good approach for one-off updates. Armen already mentioned about forcing save prior to performing action queries updating the same table, which I may do for a bulk operation rather than single record. Of course, one should be careful on deciding when to force a save - you certainly wouldn't want to force a save if the record wasn't complete. Using placeholders or such to nominally satisfy the requirements usually will leave us worse off. (Not to suggest that anybody would actually do this; just wanted to be explicit here) > One approach I've already tried is to have the Timer event of the form save > the form's record every few minutes to prevent against people leaving an > edited record unsaved for long periods of time. As Armen nicely pointed out, that could cause more problems than it solves. Remember that by default, Access uses optimistic locking so it wouldn't even be an issue if someone has a dirty record and left for a long coffee break. That would be a problem with a pessimistic locking, however! But even if we were using pessimistic locking, I would prefer that the Timer event cancel the edits (after a warning or so) rather than attempt to save what could be incomplete or erroneous record. > Thanks for the explanations, Banana. You're welcome. I'm sure others also helped to illuminate the situation. :)
From: Marshall Barton on 17 Jan 2010 11:31 Tom Wickerath <AOS168b AT comcast DOT net> wrote: >Try setting the Recordsource for the form to this SQL statement (or to a >saved query with this SQL statement): > > SELECT * FROM table WHERE 1=0 > >This query is guaranteed to return zero records. Remove the code that you >had in the Form_Load event, which was apparently running the query that >grabbed criteria from the combo box. Side note. There was a cdma thread on this issue many years ago where David Fenton posted the results of extensive performance testing of using anything that boils down to WHERE False. In my mind, he conclusively demonstrated that Jet query optimization is not smart enough to recognize that no records will be retrieved and consequently does a full table scan. An alternative that can use indexes to optimize data retrieval is to compare a unique indexed field to an impossible value. For example, while only 99.99...% guaranteed safe, WHERE {increment autonumber PK field} = 0 will be orders of magnitude faster than WHERE False. On a large table, this can make a HUGE difference. -- Marsh MVP [MS Access]
From: John W. Vinson on 17 Jan 2010 15:47 On Sun, 17 Jan 2010 10:31:55 -0600, Marshall Barton <marshbarton(a)wowway.com> wrote: >An alternative that can use indexes to optimize data >retrieval is to compare a unique indexed field to an >impossible value. For example, while only 99.99...% >guaranteed safe, > WHERE {increment autonumber PK field} = 0 >will be orders of magnitude faster than WHERE False. On a >large table, this can make a HUGE difference. That's *very* good to know, Marshall... now I'd better go change some code in a couple of my clients' applications! -- John W. Vinson [MVP]
From: Armen Stein on 17 Jan 2010 17:47
On Sun, 17 Jan 2010 13:47:43 -0700, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: >>An alternative that can use indexes to optimize data >>retrieval is to compare a unique indexed field to an >>impossible value. For example, while only 99.99...% >>guaranteed safe, >> WHERE {increment autonumber PK field} = 0 >>will be orders of magnitude faster than WHERE False. On a >>large table, this can make a HUGE difference. > >That's *very* good to know, Marshall... now I'd better go change some code in >a couple of my clients' applications! We use Where 1=0 in SQL Server all the time, and it's very fast - basically instantaneous, even on large tables. The SQL Server optimizer must be smarter than Access in this case? Armen Stein Microsoft Access MVP www.JStreetTech.com |