Performance drops after first lock
Rick Heijster
I have an issue with a MS Access database. My application is split into two
parts: the Front End, with all the forms (located on the local computers of
the users) and the Back End database, located on the server (Windows 2000
Advanced Server). As soon as any user puts a lock on the database, the
performance for *all* users drop dramatically.
Does anyone know what the cause of this may be?
Thanks in advance for all your help.
Rick Heijster
Andrew Wrigley
Therefore, it would help to know what you mean by 'first lock'
You probably need to uncheck the 'Track name AutoCorrect info' option
(Tools/Options/General Tab)
Or else, under Tools/Options/Advanced Tab, set the default open mode
to 'shared' and set the default record locking to 'no locks'.
There is KB article on the subject of Access going slow, but I can't
find it right now (there are plenty of references to the article on
google). It is worth reading.
You could also have problems with Antivirus software.
Regards
AW
Andrew Wrigley
I found the article.
Hope it helps
AW
Microsoft Access 2000 slow for more than one user and general
performance tips
Try the following suggestions as originally suggested by Frank Miller
of MS Support
and extensively updated by me. Some of these tips also apply to
Microsoft Access 97
and 2002.
The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection fixes
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
Other reasons are
- New format of Access 2000 MDB
- Place backend MDB on the root of the network share rather than
several folders
down
- Shorten the name of the backend MDB
- Miscellaneous Performance Suggestions
- Virus scanning
- System utilities
- Outlook 97 Journaling
- Queries up to five times slower if user defined functions and Jet
4.0 SP4 or 5
When every user is slow opening up the MDB before the first line of
code is run then
it likely needs a decompile.
LDB locking which a persistent recordset connection fixes
When the symptoms encountered indicate that performance is acceptable
with a single
user in the database but drops significantly when two or more users
are in the
database, the problem may be caused by interaction with the LDB file.
In Access 2000, when a second and subsequent user tries to access a
shared backend
database on the server, there seems to be a situation where Access
tries to perform a
delete on the LDB file (which fails because another user is currently
in the file).
This attempt is made about 15 times before silently failing and the
records are
returned from the linked table.
To resolve this issue we need a persistent connection to the back-end
from each of
the front-end workstations. This can be done using a bound form which
is always open
or by keeping a recordset open at all times..
Bound form
I always have a global options table containing such details as
default corporate
logo, report banding true/false and colour, bar code font file name
and so forth. I
have a hidden form bound to this table which is always open. I then
reference these
fields as appropriate in code using
forms!GlobalOptions!goReportBandingColour and
forms!GlobalOptions!goCorporateLogoPathandFilename.
Global Recordset which is always open
Alternatively you can create a dummy (test) table in the backend file
and create code
in the front-end file which opens a recordset on this table and
persist the recordset
until the front-end app is closed. To do so:
Create an empty form. .
Declare a recordset variable in the global declarations section.
In the OnOpen event open a recordset against any table.
In the OnClose event, which will fire when the MDB is closed, close
the recordset and
Set variable to nothing
Ensure you always open this form when opening the MDB. You will likely
want to open
this form hidden.
Maintaining persistent connections to linked tables could improve
performance
significantly because it prevents Microsoft Jet from constantly
deleting, creating,
and obtaining locking information from the other database's locking
information file.
Public rsAlwaysOpen As Recordset
Private Sub Form_Close()
rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing
End Sub
Private Sub Form_Open(Cancel As Integer)
Set rsAlwaysOpen = CurrentDb.OpenRecordset("DummyTable")
End Sub
Refreshing table links can also be quite slow
Refreshing the links to tables can be quite slow even in Access 97.
This can get much
worse for the second and subsequent users into a shared MDB on a
server. Once you've
successfully refreshed the first table open a recordset based on that
table. Once
you've finished refreshing all the links close that recordset.
Then open a bound form or keep this recordset open if so desired
depending on your
preference for better overall performance.
Sub datasheet Name property set to [Auto]
Thanks to Microsoft for compiling much of the following text.
Q261000 ACC2000: Slower Performance on Linked Tables indicates that if
the database
has many linked tables that also have many relationships, and the
table that you are
opening has its sub datasheet Name property set to [Auto], this can
make the table
slow to open. Subdatasheets are a new feature in Access 2000
Therefore, you are more
likely to notice this behaviour after you convert a database from an
earlier version.
It is recommended that we set the sub datasheet Name property on each
table in the
back-end database to [NONE].
Track name AutoCorrect
Tools >> Options >> General >>.Track name AutoCorrect info should be
off.
Q200600 - ACC2000: Slow Performance Opening Object with Name
AutoCorrect
One symptom is that the MDE can be much quicker when it comes to
opening forms than
the MDB. One report stated the difference was less than one second
versus four
seconds.
New format of Access 2000 MDB
Access 2000 development does experience a performance decrease (and a
related
increase of the database size) as compared to Access 97. This is
caused by the new
way Access 2000 stores project items. Project items consist of Forms,
Reports, Macros
and Modules. In previous versions, each object had its own record in
the system
table. If a change was made to an object only that one record in the
system table was
updated.
With the move to include the Visual Basic Editor interface, Access now
stores all
project items as one blob within approximately one record in the
system table. If
there are lots of code, forms and reports, then making a change to 1
object causes us
to rewrite the majority of the blob that consists of all the project
items. As a
result, more is being written to disk then was done in the past.
Some changes to the database cause Access to make a copy of the
project items instead
of replacing the old project which can cause an increase in database
size. If we have
a large project and we end up copying it then we double the size of
the project
within the database. For example, lets say we have 10 MB project and
perform an
action that causes us to make a copy of the project instead of
replacing it, the
database will grow by 10 MBs. Compacting the database at this point
should recover
the project no longer being used and should reclaim some space (if not
all 10 MBs).
The best choice to reduce the impact of this change is work on all the
database files
located on the local system and not on the server. Also make sure
you have the
fastest available reasonably priced (don't go spending lots of money
on SCSI if you
don't need to) hard drive controller systems and hard drives. Also
make sure you
have the latest drivers. I'm quite happy with the performance of my
tower systems
IDE 100 controller and hard drives. Much less so with my laptop which
is an IDE 33.
For more information see Q246306 - ACC2000: Saving Objects in DB
Slower Than in
Earlier Versions
Place backend MDB on the root of the network share rather than several
folders down.
I'd read a credible posting a number of years ago indicating that
someone who was
working on a dialup networking analyzed the packets and realized this
was a big
problem. Novell report the same problem in MS access database run
from NetWare
server excessively slow. However this could have been fixed in newer
OSs as well as
various service packs.
There was a posting the same day I wrote the above paragraph
indicating this is still
a problem. Access 2000/Windows 2000-Slow Performance....kind of solved
PB stated that shortening the path from 75 characters to 31 characters
and removing
four directory levels changed "from 50 seconds to load for the first
time and was
accelerated to unbelievable about 15 seconds." "About 3 1/2s instead
of 15s is a real
progress." They also shortened the name of the back end.
Shorten the name of the backend MDB
Yes, hard as this is to believe this has also been posted as helping.
Virus scanning
Ensure your virus scanning software only checks local drives and not
network drives.
One report stated twenty second queries took five minutes or fifteen
times as long.
Let the server's ant-virus software monitor the server.
System utilities
The following is but one example. There are others I've seen in the
past but didn't
think to record them.
Windows and applications run slower or stop responding after
installing GoBack 3.x
Personal Edition
Outlook 97 Journaling
It's been so long since I've seen this problem I'd forgotten about it
until a recent
newsgroup posting. Outlook 97, by default, saves a log of all
changes to all Word
97, Excel 97, probably PowerPoint 97 and certainly Access 97. See
Q167081 OFF97:
Opening and Closing Programs or Files May Be Slow. Also see Q167975
OL97: Office
Programs Stop Responding While Outlook Is Busy and Q166850 OL97:
Outlook Starts
Slowly with AutoJournal Feature for hints of just how bad this can be.
Queries up to five times slower if user defined functions and Jet 4.0
SP4 or 5
Q302496 ACC2000: Queries Slower After You Install MS Jet 4.0 SP4 or
SP5
Miscellaneous Performance Suggestions
Use UNION ALL in Union queries rather than just UNION.
Delete and recreate table links rather than refreshing the links. In
some
situations Access can cache too much information about the link
connection slowing
things down excessively.
The following articles address performance improvements that can be
obtained when
using Access 2000. Each of these suggestions should be reviewed and
applied as
appropriate to obtain maximum performance from Access 2000. They will
likely help in
Access 2002 as well.
Q209113 ACC2000: Tips for Improving Sub form Performance
Q209126 ACC2000: How to Optimize Queries in Microsoft Access 2000
Q210408 ACC2000: How to Speed Up Iterative Processes in Visual Basic
Q248910 ACC2000: Opening Form 100s of Times Affects System Resources
Q240434 HOWTO: Improve Performance of Applications Using Jet 4.0
Various Novell clients, including 3.1 sp2, cause Database query in MS
Access is
unacceptably slow. Note that some Novell clients can also cause
corruptions.