Access 2003 application slow when multiple users are on.

[David G.]

Details:
Access 2003
Default open Mode: shared
Default record locking: Edited record
Open database using record-level locking: True
Track name AutoCorrect: Off
Run permissions: User's

Split (FE/BE) application
BE located on "shared" drive
FE located on users' desktop
There is a persistent link that keeps the BE locking file open.

Complaint:
The application slows to a crawl when multiple users are editing any
data. (Even as few as 2 users, working on unrelated records, cause a
major slow down.) No complaints if there is only one user.
THANKS!
David G.

 

[a a r o n _ k e m p f]

uh.. I came to the same conclusion about a decade ago.. Jet just isn't
designed to support multiple users.

move to SQL Server.. run a couple of wizards for indexing.. and presto-
chango, you're going to have 10x better performance.

 

[David G.]

Aaron:
Thank you for your comments, unfortunately I am stuck with the tools
at hand. Also, the circumstances and performance degradation indicates
that something is not performing as expected, not that the tool is
insufficient for the task.

uh.. I came to the same conclusion about a decade ago.. Jet just isn't
designed to support multiple users.

move to SQL Server.. run a couple of wizards for indexing.. and presto-
chango, you're going to have 10x better performance.

THANKS!
David G.

 

[klen_]

Hello all

move to SQL Server.. run a couple of wizards for indexing.. and presto-
chango, you're going to have 10x better performance.

+1


David, could you show us your database schema.
it is possible that some actions/queries can be run on the user's computer without any decelerations.
Instead of some queries you can use temporary tables.
For example:

	Dim tmp_mdb As String
 	Dim pdb As String
 	pdb = Environ("Temp")
 	
 	If Int(Val(Access.Version)) > 11 Then
 		tmp_mdb = pdb & "\tmp_123.accdb"
 	Else
 		tmp_mdb = pdb & "\tmp_123.mdb"
 	End If
 	
 	Dim db As DAO.Database
 	Set db = DBEngine.Workspaces(0).CreateDatabase(tmp_mdb)
 	...
 	...
 	db.Close
 	Set db = Nothing

 

[David G.]

Thanks Allen. Very helpful information.
In researching the problem I had come across the other responder's
comments regarding Access. I appreciate his taking the effort to
respond, but his prejudice or bias caused him to miss the point of the
question; which, you saw and responded to quite nicely.

I learned today that the network where the application is running is
basically several PCs running XP Pro, without any central server or
any Windows Server OS.

If I understand your comments, turning off "Record Locking" doesn't
prevent Access from handling edit conflicts. In the event of an update
conflict, the users sees the message about needing to refresh the
record and given the option of discarding his changes or overwriting
the other user's changes.

Hi David.

Assuming a good, stable network that does not have excessive traffic, I've
good quite good performance from A2003 on a wired Ethernet network (not
WiFi.)

My preference is to use 'No Locks' rather than edited record. We just train
users how to handle the conflict dialog. Most of them never see it, but it
does run better if you can do it this way.

Record-level locking is also a bottle-neck. Unless you really need it, I'd
strongly suggest you turn this off. (There are even cases where action
queries executing inside a transaction won't run to completion if this is
turned on, but run fine if you turn it off.)

Beyond that, you may like to walk your way through the items in this Access
Performance FAQ:
http://www.granite.ab.ca/access/performancefaq.htm

For your own sanity, just ignore the resident troll who regularly posts to
deprecate Access.

HTH

THANKS!
David G.

 

[David W. Fenton]

If I understand your comments, turning off "Record Locking"
doesn't prevent Access from handling edit conflicts. In the event
of an update conflict, the users sees the message about needing to
refresh the record and given the option of discarding his changes
or overwriting the other user's changes.

There are two issues there:

1. record-level vs. page-level locking

2. optimistic vs. pessimistic locking.

If you turn off record-level locking, the full data pages are
locked, and this can mean that editing one record can lock other
records stored inside the same data page. However, this is not
nearly as much of a problem as it sounds like it would be -- I never
use anything else, and my users don't have edit conflicts.

Optimistic/pessimistic locking refers to what happens when a user
lands on a record that is locked by another user. With pessimistic
locking, the user can't make any changes until the other user
releases the lock. With optimistic locking, the edits are allowed
and the database engine optimistically assumes that by the time the
edits are done, the record will be unlocked by the other user. This
is yet another case where it would seem that pessimistic locking is
the way to go, but you'll end up with much worse problems than you
do with optimistic locking. Again, I never use anything but
optimistic locking in all my apps, and users almost never report
edit conflicts.

If you're having concurrency issues with page-level locking and
optimistic locking, then you may need to re-architect your
application.

- Your schema may need adjustment so that tables are partitioned
differently.

- You might be able to improve concurrency with, say, random
Autonumber as PK (since tables are clustered on the PK, this means
new records won't be clustered at the end of the table, where edits
could be happening on the same data page.

- You might need to change you some of your forms to be unbound, or
to make sure they are dirty only for brief periods (i.e., if you're
editing in code, save quickly).

- You might be better off upsizing to SQL Server or another
server-based database engine, which is not file-based and thus can
avoid certain concurrency issues that happen with Jet/ACE back ends.

There are lots of things that can be done if you're having
concurrency problems. But it's not clear that your problems will not
be fixed by one or both of Allen's recommendations (switching to
page-level locking and using optimistic locking).

 

[David G.]

David:
All of the forms have "No record locking" set in their properties. I
assume that the properties setting should over-ride Access's Options
setting for record locking. (Yes?)

I've been reading about Access 2003 record locking and how Access uses
page-level locking when Access is started by the application. (If the
application is started using Access's File --> Open commands, then
record-level locking is used.)

Since user's are clicking desktop shortcuts to open the application,
if the form properties don't over-ride Access's Options settings, it
would explain the slowdown experienced when 2 users are editing
different records.

The solution (if this is the problem) seems like an even bigger
problem. To protect the application the menu items are restricted
(Tools-->Options is not available.) Simply opening Access, without a
mdb being opened, disables the Tools-->Options menu item. Each user
would have to create a bogus mdb file, change Access Options, then
delete the bogus file.

There are two issues there:

1. record-level vs. page-level locking

2. optimistic vs. pessimistic locking.

If you turn off record-level locking, the full data pages are
locked, and this can mean that editing one record can lock other
records stored inside the same data page. However, this is not
nearly as much of a problem as it sounds like it would be -- I never
use anything else, and my users don't have edit conflicts.

Optimistic/pessimistic locking refers to what happens when a user
lands on a record that is locked by another user. With pessimistic
locking, the user can't make any changes until the other user
releases the lock. With optimistic locking, the edits are allowed
and the database engine optimistically assumes that by the time the
edits are done, the record will be unlocked by the other user. This
is yet another case where it would seem that pessimistic locking is
the way to go, but you'll end up with much worse problems than you
do with optimistic locking. Again, I never use anything but
optimistic locking in all my apps, and users almost never report
edit conflicts.

If you're having concurrency issues with page-level locking and
optimistic locking, then you may need to re-architect your
application.

- Your schema may need adjustment so that tables are partitioned
differently.

- You might be able to improve concurrency with, say, random
Autonumber as PK (since tables are clustered on the PK, this means
new records won't be clustered at the end of the table, where edits
could be happening on the same data page.

- You might need to change you some of your forms to be unbound, or
to make sure they are dirty only for brief periods (i.e., if you're
editing in code, save quickly).

- You might be better off upsizing to SQL Server or another
server-based database engine, which is not file-based and thus can
avoid certain concurrency issues that happen with Jet/ACE back ends.

There are lots of things that can be done if you're having
concurrency problems. But it's not clear that your problems will not
be fixed by one or both of Allen's recommendations (switching to
page-level locking and using optimistic locking).

THANKS!
David G.

 

[David G.]

Arvin:
Thanks. I had come across the link below and didn't find any issues,
with the exception of the subdatasheet set to "[Auto]" for the backend
tables. The information indicated this really wasn't a problem unless
the Datasheet view was being used. All the forms are using Continuous
form view.

Rest assured that Aaron has no idea what he's talking about. He's an
unfortunate individual who constantly trolls these groups.

Jet is quite suited for Multi-user use and has been since it's inception.
You problems can be associated with the database design, or with your
network.

First tackle the design. An excellent performance FAQ can be had at:

http://www.granite.ab.ca/access/performancefaq.htm

If after implementing those procedures, you are still running slow, start
checking the network, and the user's permissions on the network. Users need
pretty much all permissions except Full Control on the folder that hold the
database.

THANKS!
David G.

 

[David W. Fenton]

All of the forms have "No record locking" set in their properties.
I assume that the properties setting should over-ride Access's
Options setting for record locking. (Yes?)

Yes, and that's optimistic locking.

I've been reading about Access 2003 record locking and how Access
uses page-level locking when Access is started by the application.
(If the application is started using Access's File --> Open
commands, then record-level locking is used.)

....if Access is set to use record-level locking.

Since user's are clicking desktop shortcuts to open the
application, if the form properties don't over-ride Access's
Options settings, it would explain the slowdown experienced when 2
users are editing different records.

It would mean they are using record-level locking, maybe.

The solution (if this is the problem) seems like an even bigger
problem. To protect the application the menu items are restricted
(Tools-->Options is not available.) Simply opening Access, without
a mdb being opened, disables the Tools-->Options menu item. Each
user would have to create a bogus mdb file, change Access Options,
then delete the bogus file.

I strongly doubt this is the actual cause of your performance
problem. Had you gone through the whole performance FAQ at the link
you were given earlier?

 

[David W. Fenton]

I had come across the link below and didn't find any issues,
with the exception of the subdatasheet set to "[Auto]" for the
backend tables. The information indicated this really wasn't a
problem unless the Datasheet view was being used. All the forms
are using Continuous form view.

I don't know that that is true. I'd turn it off, anyway.

 

[John Spencer]

If you are using continuous form view and are working with all the records in
the table instead of a small set of records (one is ideal) then this can cause
significant performance issues.

As Arvin remarked, the problem may not be the network so much as your
application design.

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

Arvin:
Thanks. I had come across the link below and didn't find any issues,
with the exception of the subdatasheet set to "[Auto]" for the backend
tables. The information indicated this really wasn't a problem unless
the Datasheet view was being used. All the forms are using Continuous
form view.

Rest assured that Aaron has no idea what he's talking about. He's an
unfortunate individual who constantly trolls these groups.

Jet is quite suited for Multi-user use and has been since it's inception.
You problems can be associated with the database design, or with your
network.

First tackle the design. An excellent performance FAQ can be had at:

http://www.granite.ab.ca/access/performancefaq.htm

If after implementing those procedures, you are still running slow, start
checking the network, and the user's permissions on the network. Users need
pretty much all permissions except Full Control on the folder that hold the
database.

THANKS!
David G.