Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Optimizing a Split DB (2k)

    Hi, I have a split database that has a front end on the user's PC and the back end on a server. Unfortunately, the database has become slow with the split, and it takes a while for some things to load, especially during start up. I was wondering if anyone had some tips on speeding up the speed a database loads and works from a server.

    Some other information:
    -running an unsplit database from the server is fast
    -my current DB uses DLookUps to find out users permissions whenever they try to run a form from the switchboard. (it seems these cause the majority of the lag)
    -running append and update queries are really slow
    -the DB averages around 10-15 users on at a time
    -the front end contains all static tables
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimizing a Split DB (2k)

    Francois

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Optimizing a Split DB (2k)

    It is a good idea to have a persisten connection between the frontend and the backend. For example, open a form based on a backend table when the frontend opens, and keep it open until the frontend is closed (I use the Switchboard form for this), or open a recordset based on a backend table when the database is opened, and keep it open until the frontend is closed. Either of these will avoid continually accessing the .ldb file.

    What do your users do? If you have 12 (or even more) users browsing data and 3 entering or editing data, it shouldn't be a problem, but 3 browsing and 12 editing is already a heavy load for Access.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimizing a Split DB (2k)

    Francois, I'll check that link out later today, thanks!

    Hans, I have a switchboard and I actually use FMS' Admin software which I believe keeps an active link to the BE at all times. As for what my users are doing, I think most of them use it half for editing and half for viewing. At the company I work for, there used to be tons and tons of unorganized excel spreadsheets keeping track of different data, and there would even be different versions of these different sheets, but everything was supposed to relate to each other thing. I came up with the database solution to standardize everything. So everyone who used these excel sheets now have their information stored in the database, and they edit /view it as much as they would in their old excel spreadsheets. I should note that we have a couple of clerks as well who use it solely for data entry.

    Aside from the users, the database takes a while to load even when there is NO one in the database (not as long, but definitely a noticeable lag), which leads me to believe that it is because its not properly optimized.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Optimizing a Split DB (2k)

    If the database takes a long time to load without anyone else in it, check on subdatasheets and turn them off in both front and back ends. I've seen that kind of performance drain on a large database with subdatasheets.
    Charlotte

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,430
    Thanks
    1
    Thanked 33 Times in 33 Posts

    Re: Optimizing a Split DB (2k)

    Also make sure "Track Name Autocorrect" is turned off. (In Tools | Options, General tab.)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimizing a Split DB (2k)

    I feel for you officespacer. I've done everything suggested and still can't speed up things sufficiently.

    My problem appears to be network based rather than simply an Access 'thing'. Even being the only one on the system it is sometimes quick and sometimes slow with no obvious explanation for the difference. However when the accounts people are in and working my Access program is always slow (the commercial accounts package uses Foxpro as its db engine) so I suspect that there are lots of non-Access things going on on my network. You may wish to look at this possibility.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Optimizing a Split DB (2k)

    This sounds rather much like the lock file problem that surfaced with Access 2000, and the persistent connection is supposed to solve. I would be sure to double-check the FMS software - as far as I know it only has a connection to the back-end database from the workstation where it is being run. Without knowing more about your application, it's hard to suggest possibilities beyond what has already been identified, but there are some additional resources on these kind of problems. You might want to look at FMS Performance Tips in addition to Tony's site that Francois suggested. Both are focused on older versions of Access, but most of what they suggest is still appropriate.

    If you continue to see performance issues, you may want to consider using a SQL Server back-end, and you may want to look at performance aspects of your LAN. When we see 100Mbit LANs, performance is usually close to being as good as running on a local hard drive.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •