Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: Database Performance Problem

    I have created a database in two MDB files. One file ("application") contains all of the queries, forms, reports, etc. It links to a set of tables in the second MDB file where I am storing all of the data. I set this up so that the "application" MDB file could reside on the users local hard drive and access a common database that is shared on the server.

    I did this to improve performance and allow mulitple users to access the system. However, performance is very slow across the network.

    A friend suggested I move the linked tables into the application and don't access the data as a seperate file. Will this improve performance?

    Thanks,

    Joe

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Good design is to split it. Keeping it split will make it easier for you to update the forms and reports. You can make changes on your system, test them using the live data then have your users get a copy of the front end. Much easier than having everyone get out of the application and then importing the objects that changed without losing any data that was changed in the common database.

    If you want several people to use the database then it has to be on the server. Regardless of whehter the front end is on the user's system and the backend is on the server, or everyone is accessing the same front end on the server the same problem exists, the network. I would look at optimizing queries and forms.

    How slow is slow? How much data do you have? Is it all in one table? Do you have enough indexes on the tables?

  3. #3
    Join Date
    Jan 2004
    Posts
    5
    The database is a new customer service system and I only have about 20 test records input. To open the main customer screen (contact info, and a sub-form that displays summary of previous requests) takes about 10 seconds to open. But to open the form that has the detail information of the request from the main customer form takes about 1 minute to load.

    I spoke to the IT and it turns out the location with the performance problem is accessing the network through a "dual-ISDN" line at about 1 megabit. I don't if any design changes to the system will compensate for slow bandwith.

    I was hoping to find out linking to external tables really impacts performance that much.

    Thanks.

  4. #4
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Two initial thoughts:

    Try to move look-up tables that have close-to-static content to the client app. And "synch" them with the server "in the middle" between the other clients at an appropriate frequency.

    When you save a new version of your client app, I assume that you do a compact and repair before you distribute. A trick to speed up the performance is to make just one tiny change to a form just afterwards, save and then distribute the md(e?). (for example just drag the transparent frame of a label or something, anything that allows a new small save).

    Daniel

Posting Permissions

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