trying to minimize Write Conflicts in a multi-user database
in [General]
|
Prev: não acho meu orkut
Next: maike yordano pirela vera
From: Marshall Barton on 17 Jan 2010 18:15 John W. Vinson wrote: >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! I am not going to set up the test cases now, but I'm wondering if using an inner join to an indexed empty table might be just as fast and guaranteed to work in all cases. -- Marsh MVP [MS Access]
From: John W. Vinson on 17 Jan 2010 18:40 On Sun, 17 Jan 2010 14:47:13 -0800, Armen Stein <ArmenStein(a)removethisgmail.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? So do I, usually, and haven't had any real problems. I'm not sure if it's just that the query is "fast enough" for my tables or if the optimizer sometimes guesses right! -- John W. Vinson [MVP]
From: Paul on 17 Jan 2010 20:34 Armen, Banana, Ok, I'm persuaded that I should get rid of the saving the record based on the timer. I started doing it out of desperation over how to solve the Write Conflict messages. But as I read through the suggestions made by all of the experts in this thread, I'm optimistic that the problem may go away when I incorporate those suggestions in my application. In particular: - Moving memo fields into 1 to 1 tables, - Loading one record at a time into the main form, instead of loading the entire recordset and - Making sure I'm not running action queries on the same record I'm editing in the form without saving the record beforehand. Thanks
From: Armen Stein on 17 Jan 2010 21:28 On Sun, 17 Jan 2010 17:34:17 -0800, "Paul" <begone(a)spam.com> wrote: >- Moving memo fields into 1 to 1 tables, >- Loading one record at a time into the main form, instead of loading the >entire recordset and >- Making sure I'm not running action queries on the same record I'm editing >in the form without saving the record beforehand. I've heard the first suggestion many times, but we've never done it and our apps work just fine. I certainly recommend the other two. Maybe you could try them first before making structural changes. Armen Stein Microsoft Access MVP www.JStreetTech.com
From: Paul on 17 Jan 2010 23:43
Great suggestion, Armen. Changing the tables is something that will take days of modification and debugging, so it would require the most time and effort. I'll try the other two first, and see what results. Thanks much. Paul "Armen Stein" <ArmenStein(a)removethisgmail.com> wrote in message news:kih7l5hc1sk37600tvi57f6oec0lb07ujl(a)4ax.com... > On Sun, 17 Jan 2010 17:34:17 -0800, "Paul" <begone(a)spam.com> wrote: > >>- Moving memo fields into 1 to 1 tables, >>- Loading one record at a time into the main form, instead of loading the >>entire recordset and >>- Making sure I'm not running action queries on the same record I'm >>editing >>in the form without saving the record beforehand. > > I've heard the first suggestion many times, but we've never done it > and our apps work just fine. > > I certainly recommend the other two. Maybe you could try them first > before making structural changes. > > Armen Stein > Microsoft Access MVP > www.JStreetTech.com > |