trying to minimize Write Conflicts in a multi-user database

[Armen Stein]

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

 

[Banana]

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.

 

[Marshall Barton]

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.

 

[John W. Vinson]

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!

 

[Armen Stein]

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

 

[Marshall Barton]

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.

 

[John W. Vinson]

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!

 

[Paul]

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

 

[Armen Stein]

- 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

 

[Paul]

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

 

[Banana]

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.

Thanks for sharing. That certainly did piqued my curiosity and decided
to try for myself, mainly to see whether it was still true today as it
was 'many years ago'.

On a 64-bit Access 2010 & Win2008 OS with 2 GB RAM & 320 GB 7200 RPM HD,
I imported into a table containing 2.8 million rows of data or about 110
MB worth, and set up a VBA routine to perform several trials of 3 criteria:

WHERE PK = 0
WHERE PK = 0.1
WHERE 1 = 0

Looking at the ShowPlan output, it would seem that the first two would
perform better than last one since it states index is used while last
one does a table scan. I threw in 0.1, reasoning that since autonumber
were integers, 0.1 would be actually a better guarantee should someone
dare to insert 0 into the primary key but this obviously means an
implicit conversion, so... Anyway, just to pique my curiosity.

However, I was unable to show that the time required to open a empty
recordset was consistently in favor of any one of those expression.
Sometimes 1=0 is the fastest, sometimes, it's 0.1 then sometimes it's 0.
However, in all cases, none of those expressions exceeded 100
milliseconds and generally floats between 3 ranges; 15-ish, 30-40-ish
and 60-70ish nearly equally among three possible criteria.

The next thing I did was to cross reference David's results, but
unfortunately I have been unsuccessful in locating the CDMA thread
cited. I would love to see what he did and verify I did not miss
anything important.

It's too early to conclude anything but from those trials I did, I am
not quite convinced that 1=0 (which get transformed into "Not 0=0" in
ShowPlan, BTW... other variations gets the same transformation) is going
to be much slower than PK = 0 or PK = 0.1. The ShowPlan may say
1=0/False/Not 0=0 forces a table scan, but I think we have to question
whether it is a case of ShowPlan not being completely honest or
detailed, especially considering that we were working with 2.8 million
records, which I certainly think would be noticeable if table scan were
actually done.

If I could get a link to that CDMA thread, I would be quite delighted.

 

[Tom Wickerath]

I have also run JET ShowPlan tests on the 1= 0 criteria, and indeed, I
noticed the table scans in the Showplan.out files. But, like Banana, the
performance *seemed* very fast. None of the tables in databases I work on at
work are all that huge, so I wasn't ready to make any conclusions based on
those results. But, at my place of work, we are using WANs whether we like it
or not, since most of the servers for shared folders that I use are located
in Bellevue, WA. and I work in Seattle. Still, the performance is very fast
and rock solid.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Banana]

However, I was unable to show that the time required to open a empty
recordset was consistently in favor of any one of those expression.
Sometimes 1=0 is the fastest, sometimes, it's 0.1 then sometimes it's 0.
However, in all cases, none of those expressions exceeded 100
milliseconds and generally floats between 3 ranges; 15-ish, 30-40-ish
and 60-70ish nearly equally among three possible criteria.

A follow-up-

I wanted to cover two more bases:

1) I tested how much time it would take to evaluate the "Not 0=0" by
doing a For..Next loop with as many iterations as there were records in
the table. While this is not an apple to apple comparison, it should
give us some idea of how much time it takes to evaluate the Not 0=0 and
thus give us some idea of whether table scanning is actually being
done. In my first tests, I got roughly average of about 90-100
milliseconds, which is certainly slower than my reported trials for the
Not 0=0 but I do not consider this to be statistically significant so...

2) I decided to be really safe and expand my table from 2.8 millions
into 11.4 millions or about 435 MB and re-run the trials. Reasoning that
if the table scanning actually were being done, the time needed to
execute 1=0 should increase. In those trials, it did not and remained at
the same averages I reported last night with the other two criteria. The
For..Loop did increase to 400-500 milliseconds which is now significant
enough to warrant questioning whether ShowPlan is telling the complete
story when it claims that it does a table scan for 1=0/False/Not 0 = 0.

 

[Marshall Barton]

Thanks for sharing. That certainly did piqued my curiosity and decided
to try for myself, mainly to see whether it was still true today as it
was 'many years ago'.

On a 64-bit Access 2010 & Win2008 OS with 2 GB RAM & 320 GB 7200 RPM HD,
I imported into a table containing 2.8 million rows of data or about 110
MB worth, and set up a VBA routine to perform several trials of 3 criteria:

WHERE PK = 0
WHERE PK = 0.1
WHERE 1 = 0

Looking at the ShowPlan output, it would seem that the first two would
perform better than last one since it states index is used while last
one does a table scan. I threw in 0.1, reasoning that since autonumber
were integers, 0.1 would be actually a better guarantee should someone
dare to insert 0 into the primary key but this obviously means an
implicit conversion, so... Anyway, just to pique my curiosity.

However, I was unable to show that the time required to open a empty
recordset was consistently in favor of any one of those expression.
Sometimes 1=0 is the fastest, sometimes, it's 0.1 then sometimes it's 0.
However, in all cases, none of those expressions exceeded 100
milliseconds and generally floats between 3 ranges; 15-ish, 30-40-ish
and 60-70ish nearly equally among three possible criteria.

The next thing I did was to cross reference David's results, but
unfortunately I have been unsuccessful in locating the CDMA thread
cited. I would love to see what he did and verify I did not miss
anything important.

It's too early to conclude anything but from those trials I did, I am
not quite convinced that 1=0 (which get transformed into "Not 0=0" in
ShowPlan, BTW... other variations gets the same transformation) is going
to be much slower than PK = 0 or PK = 0.1. The ShowPlan may say
1=0/False/Not 0=0 forces a table scan, but I think we have to question
whether it is a case of ShowPlan not being completely honest or
detailed, especially considering that we were working with 2.8 million
records, which I certainly think would be noticeable if table scan were
actually done.

If I could get a link to that CDMA thread, I would be quite delighted.

I tried to search Google Groups, but that seems to have lost
its ability to find stuff in the archives. David, if you
are following this thread, we would appreciate it if you can
dredge up that test and re post your findings.

Banana, he performance results you and Tom are reporting are
very interesting, but I am not totally convinced at this
time. Performance testing can be very difficult and tricky,
especially with things that involve I/O. When caching is
used, the first run after a boot can be drastically
different than subsequent runs. Today's multi core
processors and seriously fast multi gigabyte memory may very
well make a cached table scan quick enough, maybe nearly as
fast as an index scan.

Then there is the issue of what other active processes are
also using the system's resources. A full table scan in
cached memory on a lightly loaded system might be pretty
fast, but when there are other processes making large
demands for processor cycles and memory, the result could be
a completely different story.

Tom's performance over a WAN (how fast?) is interesting and
warrants further analysis under varying user scenarios.

More than a little intriguing is the show plan use of Not
0=0 when the query used some other expression for False.
This strongly implies that the query optimizer can recognize
expressions that evaluate to False. Why it would then say
it will use a full table scan seems contradictory to me.

 

[John W. Vinson]

More than a little intriguing is the show plan use of Not
0=0 when the query used some other expression for False.
This strongly implies that the query optimizer can recognize
expressions that evaluate to False. Why it would then say
it will use a full table scan seems contradictory to me.

I wonder if it's the same effect that causes references to Rnd() in a query to
return the same value for all rows: the query engine determines that no table
field is included in the expression and evaluates it only once. Might the same
be the case with 0=0 or False as a criterion?

 

[Marshall Barton]

I wonder if it's the same effect that causes references to Rnd() in a query to
return the same value for all rows: the query engine determines that no table
field is included in the expression and evaluates it only once. Might the same
be the case with 0=0 or False as a criterion?

Good thought John. Seems like very logical behavior, until
they fail to use that info and do a table scan anyway (if
that's still what happens?)

 

[Banana]

I tried to search Google Groups, but that seems to have lost
its ability to find stuff in the archives. David, if you
are following this thread, we would appreciate it if you can
dredge up that test and re post your findings.

Thanks for the efforts- I was worried I may have had missed something
obvious in my attempt. I really wish there was a better archiving
solution. Ironically enough, when I was searching the archive, I ran
across a old thread where the poster said she couldn't find a cited
thread even with deja.com, and that was a decade ago! Guess nothing much
really has changed.

Banana, he performance results you and Tom are reporting are
very interesting, but I am not totally convinced at this
time. Performance testing can be very difficult and tricky,
especially with things that involve I/O. When caching is
used, the first run after a boot can be drastically
different than subsequent runs.

I don't doubt there will be many variables that could skew the tests
which was why I wanted to cross reference David's test to check what I
could have had missed.

FWIW- using OLE automation to start up an Access application and run
only one query using CurrentDb.Execute (e.g. no saved query), then
immediately closing, quitting and cleaning up does not appear to
materially affect the time. The Showplan seems to suggest that a new
instance of engine was started up via OLE Automation, so this should
reflect the boot time/cold start/no caching, I'd think.

Today's multi core processors and seriously fast multi gigabyte memory may very
well make a cached table scan quick enough, maybe nearly as
fast as an index scan.

I'm not so sure about multi-cores/CPU processing. AFAIK, Access
continues to be single-threaded so it'd always use one core so the
benefit of mulitple cores/CPU would be indirect in that OS may be able
to allocate competing resources to other cores/CPUs but beyond that, not
much difference. Note that while JET/ACE has a setting for numbers of
threads, KB articles suggests that thread safety was achieved only if
you use DAO or OLEDB provider and even so, thread-safety is limited.

I also tried tweaking the threads setting from 1 to 4 (default is 3) and
the timing was not affected at least slightest, suggesting to me that
threading was not relevant here.

KB Article:
http://support.microsoft.com/kb/169395
http://support.microsoft.com/kb/299973

As for processor & memory enabling a table scan to be as fast as index
scan, this was reason why I did extra check of doing a For...Next Loop
to get a rough idea of how much time it would take to do the false
comparisons that many time. If table scan actually were being done, the
time should have had increased when I expanded the tables. It didn't
while the For...Next loop increased just as much as the increase of
records. As a further comparison, doing a "Not PK = PK" was ridiculously
slow, around 40,000 milliseconds. It's also unfair because of comparing
two variables rather than two hard coded values, but I think the point
remains- if 1=0 actually did do a table scan, it should have been just
as slow as the For...Next loop at least and certainly four times slower
after the table expanded fourfold.

Then there is the issue of what other active processes are
also using the system's resources. A full table scan in
cached memory on a lightly loaded system might be pretty
fast, but when there are other processes making large
demands for processor cycles and memory, the result could be
a completely different story.

I suppose I could try the test again by re-allocating only 512 MB to the
VM and see what happens.

More than a little intriguing is the show plan use of Not
0=0 when the query used some other expression for False.
This strongly implies that the query optimizer can recognize
expressions that evaluate to False. Why it would then say
it will use a full table scan seems contradictory to me.

Well, I've kind of found Showplan to be quite lacking in the details.
For instance, it still does not support showing plans when subqueries
are used, despite having had went through 2 more JET versions and 2 ACE
versions. Another telltale sign is that when the ShowPlan starts up, it
records the Date of 0, suggesting to me that any bugs, issues and/or
enhancements associated with ShowPlan wasn't considered high-priority
and for those reasons, I think it is prudent to take the Showplan's
output with a grain of salt.

 

[John Spencer]

Can you send me an email address so I can send you a zipped sample database?

If you post it here make sure you "munge" it up so the spammers cannot harvest
it.

Do something like
P aul// <AT> some-//-Domain.Remove.org

Please remove extraneous spaces and characters. Or tell me what it is using

Send the email to jspencer
and the remainder of my address after the at is:
hilltop.umbc.edu

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Tom Wickerath]

Well, I've kind of found Showplan to be quite lacking in the details.

For instance, it still does not support showing plans when subqueries
are used, despite having had went through 2 more JET versions and 2 ACE
versions. Another telltale sign is that when the ShowPlan starts up, it
records the Date of 0, suggesting to me that any bugs, issues and/or
enhancements associated with ShowPlan wasn't considered high-priority
and for those reasons, I think it is prudent to take the Showplan's
output with a grain of salt.

I would sure like to see improvements made to the ShowPlan functionality.
Perhaps several of us can put that forward as a suggestion to the Access Team.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Tom Wickerath]

Hi Marsh,

I tried to search Google Groups, but that seems to have lost
its ability to find stuff in the archives. David, if you
are following this thread, we would appreciate it if you can
dredge up that test and re post your findings.

I sent a private e-mail to David late last night, inquiring about any past
test results. He replied as follows:

"I don't recall anything of the sort, and don't see it in Google
Groups. Nor do I have archives of my posts from way back when.

Sorry I can't help. Maybe Marshall has a better reference?"

Performance testing can be very difficult and tricky,
especially with things that involve I/O.

I agree. When I am doing such testing at work, I typically reboot several
times, between each test, and I make sure to shut down as many applications
as I can, including Outlook, to try to make a somewhat stable baseline. On my
work PC, I cannot shut off the antivirus, so I just have to live with the
possibility that it may be adding some network load.

I think a good way to test the 1=0 question might be to start with a really
large table, like Banana did, and monitor the amount of data transferred for:

1.) An intentional table scan
Run some query that forces all records to be brought over a network wire,
perhaps an aggregrate query to sum a numeric field.

2.) WHERE conditions that include 1=0, WHERE False, etc.

One needs to first display the network monitor icon in the System Tray. In
Windows XP, one does this via:

Control Panel | Network Connections

Double-click the connection of interest, and then left-click on Properties.
Select the check box for the option that reads "Show icon in notification
area when connected". When you double-click the resulting System Tray icon,
you will see Packets Sent and Packets Received values. With a baseline that
is as repeatable as possible (ie. Outlook and any other networked
applications shut down), read the Packets Received value before and after
each individual test. The difference (delta) represents how many packets of
data was transferred to solve each query. Of course, one must keep in mind
that some other application that you may not be able to shut down may have
caused some of the traffic for a given test. So, one can run the test several
times, in an attempt to make sure there is not a contributing influence from
some other application. You need a split application, with the BE on a
network share, in order to use this method. Several years ago, John Viescas
recommended this method to me as a way of testing how much data actually
comes over the network wire.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________