Accessing DB is slow

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

I recently moved an Access database from a client
workstation to a server. There are about 10 clients
accessing the database on the server over the network via
a share on the server. Since it was moved, accessing most
areas of the databse has become slow. When it was on the
client workstation, it was not slow. Nothing has changed
as far as the databse is concerned, it has just been moved
to a faster machine. Any ideas on why it would be slow on
the server and not on the client workstation?
 
Hum, usually a network is a good deal slower then is a hard disk.

In fact, if your network was as fast as your hard disk..then why would
anyone ship hard disks in each computer..why not just run everything off of
the network?

So, a network is good deal slower then your hard disk. This means any weak
spot...or part of your application that requires a lot of data to work with
may have to be looked at. Since the network is much slower, then any part of
your application that is sluggish on your local pc will likely be TOO slow
on the network. Thus, as a habit..you should develop and test using your
network often during development. Especially in those parts that have a bit
of delay on your local pc.

There are a few things you can, and should do. There a great check of
performance issues and tricks at Tony's site..and these tricks will often
fix your application speed problems.

Also...in a multi-user environment..you need to split your database into two
parts. The front end part goes on each work station (this will eliminate the
need to transfer code, forms and the application part down the network to
your local pc). Also, splitting increase reliability...as you DO NOT want
multiple users in the same front end.

You can read up on the splitting here:

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

The other thing is you need to keep a connection open to the tables at all
times when you split.
This speed up tip..and a few others can be found at:

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

Albert D. Kallal (MVP)
 
Thanks. I know that accessing something over a network is slower than the local hard disk. You must have misunderstood my explanation. When the DB was on the client workstation - before it was moved to the server - others were accessing it over the network on that machine. It has now been moved to a server, where people still access it over the network. It is just much slower now than before it was moved.
 
Brad said:
Thanks. I know that accessing something over a network is slower than the local hard disk. You must have misunderstood my explanation. When the DB was on the client workstation - before it was moved to the server - others were accessing it over the network on that machine. It has now been moved to a server, where people still access it over the network.
Click to expand...

No, your explanation didn't explicitly state that there were users
accessing it from a local workstation rather than the server. Thus we
assumed that the first situation was one user and then you made it
available for others to use on the server.
It is just much slower now than before it was moved.
Click to expand...

Those links Albert mentioned will still help.

What OS is the workstation on which it used to reside and your server?

How many levels down is it in the path? O:\abc\def\ghi\jkl\mno etc.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
The workstation OS the DB was on before it was moved was XP Pro. The server OS is Windows Server 2003. The DB is located in D:\folder1\folder2\db.mdb. It is shared as \\servername\sharename\db.mdb and clients access the DB on the server through the share. I will try those suggestions, but why should I if it worked fine on the workstation before it was moved? Could you tell me what would cause this?
 
Brad said:
The workstation OS the DB was on before it was moved was XP Pro. The
Click to expand...
server OS is Windows Server 2003. The DB is located in
D:\folder1\folder2\db.mdb. It is shared as \\servername\sharename\db.mdb
and clients access the DB on the server through the share. I will try those
suggestions, but why should I if it worked fine on the workstation before it
was moved? Could you tell me what would cause this?

Likely, it is a issue of security. When users open file, or work on a
file..a HUGE amount of file security checking can take place (and that
process is quite slow...you can see the ldb file get created when you open a
database)). When a user opens the database, or table..ms-access then creates
a locking file on the hard disk....however, in a multi-users
environment..that lock file may already exits...but access still tries to
get a full use of that locking file. With the huge over head of additional
security..then your application slows down to the speed at which a file can
be created, or locked..and that can be very slow indeed. ms-access will try
for quite a bit of time to get that full lock on the file..and that takes
forever.

So, the #1 trick is force, or keep open a connection from your front end..to
the back end. Keeping this connection open thus eliminates all the issues of
trying to get a file lock on a file across a network. You might get a slight
initial delay as the application loads..but once you force the connection to
stay open..then performance will return to your previous levels.

So, try forced the connection trick mentioned at Tony's site..and see how it
works...

The other thing to check is if the server has any virus software running...

You can also check if you are running a domain server..and again that can
cost overhead
 
OK, thanks for the input. The server IS a domain server. Is there anything I can do inside of Windows to possibly speed the process up? Thanks again.
 
OK, thanks for the input. The server IS a domain server. Is there
Click to expand...
anything I can do inside of Windows to possibly speed the process up?
Thanks again.

yes..and as I mentioned..there is likely tons of security checking that goes
on ..that did not have to happen before. (so, as I guessed, your setup is
night and day different from the other network).

So, no..unless you can change the basic fundamentals of your network...not
much you can change.

However, as mentioned..why do you need to bother changing the server side?

You will get equal, or better performance if your force the connection open
by that trick mentioned. Once that connection is made..then all of that
security junk does NOT occur over an over again. So, the trick/solution is
to simply force the connection to stay open..and thus eliminate the security
overhead stuff from occurring each time ms-access opens a table. Forcing a
connection open means that ms-access only goes through that big security
mess once....and then from them on...you should get the same performance you
had before.

At most..this is a few lines of code in your startup routines. You can even
open a form...and minimize..and turn off the visibility. This is much less
work and time it takes to write this post!
 
Is the AuotCorrect feature checked? If so uncheck it.

It's in Tools, Options, General.

It'll slow it way down...

;-)


Brad said:
Thanks. I know that accessing something over a network is slower than the
Click to expand...
local hard disk. You must have misunderstood my explanation. When the DB
was on the client workstation - before it was moved to the server - others
were accessing it over the network on that machine. It has now been moved
to a server, where people still access it over the network. It is just much
slower now than before it was moved.
 
Back
Top