INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

slow database

slow database

(OP)
Hello, I have a database which is split into back and front ends.  It works very fast when both parts are on a local machine but when I put the back end on a server, it slows down considerably. Any suggestions?

RE: slow database

(OP)
Hey MajP,

As you probably guessed, this is for that payroll grid database that you largely helped me with. As soon as I split it into front and back ends and moved the back end onto the server, it slowed way down.  The 6 week schedule form takes upwards of 25 seconds to load.  Thanks for the links, I'll try each suggestion one at a time and see if it helps.

RE: slow database

What version of Access?  There are some version specific issues.

RE: slow database

(OP)
It's Access 2002 but database is running in Access 2000 format. Anything specific to that?  Thanks.

RE: slow database

Yes. Access 2000 and 2002 were horrible with the
 "track name autocorrect" and the
 "sub data sheet issue" and the
 "persistent lock".  

These are discussed in the first couple bullets of the first link.  The name autocorrect and the subdatasheet issue alone would make the database unuseable.  Apply those first three at a minimum. The rest become an art.  There are hundreds a little things to try as these post list.  Most of the time these do not make a huge difference, but every once in a while one of these little fixes will make a huge difference

Running on a network is almost always going to be slower, depending on the speed of the network.  Applies these and see what happens.  This may be as good as your going to get.  Try a simpler database on the network and see if you get OK performance.

The other thing, the form and code is not optimized. The code took the simple route.  When you update a record or change the date it basically reloads the form instead of just updating the record in the "grid table". This could be modified so that when you add/edit/delete a shift it just updates the record in the "grid table".  The code would be more complex, but pretty similar to the existing write to grid code.

If other forms work OK and it is only this one with the performance problems, then I would look at modifying the code. If all forms are suffering then spend the time on these other fixes.

RE: slow database

(OP)
After some trial and error, it turns out the main problem was with the back end being too far down the network path.  This was reported in Novell support. I just had to map a direct path to my back end so it appears as only one drive letter.  This cut down the form load time from 25 seconds to 4 seconds. Not bad!

I am very interested in optimizing the code however.  It is frustrating to have even a little delay when changing or adding records. Where do I start?

RE: slow database

(OP)
Thank you jedraw.  This was just what I needed.  Shortly after using the Novell drive mapping tip, which sped up my database, my workplace decided to migrate everything over from Novell to Microsoft.  This slowed everything down again!

So I skimmed through your link and the first thing that caught my eye was "Keep Static Data Local".  WTF was I thinking? It wasn't even so much the static data tables, but that I had my temp data tables across the network instead of local. Now my database runs super fast and even my largest form takes under 2 seconds to load.

Thanks again to all of you guys for your help!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close