database slow down

  • Thread starter Thread starter Alan B. Densky
  • Start date Start date
A

Alan B. Densky

Hello:

I have a client who is complaining that his Access 97 databases have slowed
down since changing his file server's OS from Win 98 to Win 2000 Server.

Does anybody have a fix for this?
 
Try the following:

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 can create persistent connection to the
back-end from each of the front-end workstations. To implement this
change, we 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:

1. Declare a recordset variable in the global declarations section.
2. Execute code from an Autoexec macro or startup form that will...
3. Close the recordset and
4. Set variable to nothing when exiting DB

Maintaining persistent connections to linked tables could improve
performance significantly because it prevents Microsoft Jet from
constantlydeleting, creating, and obtaining locking information from
the other database's locking information file.

=======================

Actually I just use a Global Options form which is always open against
the backend. So this may work as well.
For some more details on other things:
===========================
Access 2000 performance may not be as good as it was in Access 97 for
a number of reasons, most of which are outlined in the following
articles:
Q261000 - ACC2000: Slower Performance on Linked Tables
http://support.microsoft.com/support/kb/articles/q261/0/00.asp


This article 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 behavior 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] .


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.


Q209113 - ACC2000: Tips for Improving Sub form Performance


http://support.microsoft.com/support/kb/articles/q209/1/13.asp


Q209126 - ACC2000: How to Optimize Queries in Microsoft Access 2000


http://support.microsoft.com/support/kb/articles/q209/1/26.asp


Q210408 - ACC2000: How to Speed Up Iterative Processes in Visual Basic


http://support.microsoft.com/support/kb/articles/q210/4/08.asp


Q248910 - ACC2000: Opening Form 100s of Times Affects System Resources


http://support.microsoft.com/support/kb/articles/q248/9/10.asp


Q240434 - HOWTO: Improve Performance of Applications Using Jet 4.0


http://support.microsoft.com/support/kb/articles/q240/4/34.asp


Access 2000 takes significantly longer to open a complex query in
design view or to make design changes.


Access 2000 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, we now
store 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 us 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 to do all the
development of the database with all the database files located on the
local development machine and not on the server.


For more information, please see the following article:


Q200600 - ACC2000: Slow Performance Opening Object with Name
AutoCorrect


http://support.microsoft.com/support/kb/articles/q200/6/00.asp


Q246306 - ACC2000: Saving Objects in DB Slower Than in Earlier
Versions


http://support.microsoft.com/support/kb/articles/q246/3/06.asp
 
Joe, thanks for your advice. However the problem is not with an A 2000
database, it is with an A 97 db as stated in my posting.

I am well aware of the A 2000 problem and work around, and I actually
include it in my A 97 databases so that when the client asks me to upsize to
A 2000 the problem never has a chance to occur.

So, thanks again, but I still need someone to help me with this problem.

Alan
 
Are you saying you use/apply the "keep" the back end opened in a97 also?

I want to stress that this "trick" also works very well in a97, and has
solved virtually all of my performance problems problems when moving a 97
applction to a newtwork that runs a win2000 server.

So, yes..try the a97 keep the table open trick...it should help.

As for others stating that a win2000 server is slower then a win98 box, boy,
I not seen anything remote close to that experience.

In fact, running a97 on a bunch of winXP boxes and a win2000 server I found
the performance to be absolute stunning. In fact, my applciton ran as fast
on thieir new network as did when I run the access applction on a local pc
with no network! I could not tell the difference between the two.
 
A couple of years ago, I had started on a project to use local
table copies to speed up an application, when I noticed that the
network was set to "10" not "100". After a couple of calls,
the application was just as fast with linked tables as it was
with local data.

With another very file and memory intensive application, Win2K
workstation was noticeably faster than WinME on the major batch
process (all local files and tables).

I have seen lots of slow servers. One of the things that probably
slows Access down is 'network optimisation'. Most network
optimisation doesn't seem to be suitable for a shared Access
database. Another thing is 'network security'. Access doesn't
go well with deep folder structures and permission checking at
every level. Another thing is 'centralised servers': centralisation
always seems to mean moving the server farther away from the
user.

Win2K server explicitly contains optimisations for Single User
connections, and for Very Large Numbers of connections. A typical
Access application falls between those, benefits from neither
set of optimisations, suffers from the overhead of those
optimisations, and suffers from the slower file system and increased
security overhead.


(david)


)
 
Back
Top