Results 1 to 4 of 4

Thread: Database Tuning

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have been asked to help tune an Access 2003 database. Right now the database functions, but when trying to do some of the functions for the full company, the database will hang.

    Does anyone have suggestions of things to look for or a resource that would help with tuning this monster?

    Will adding indexes to the tables help? (some tables have primary keys, some do not)
    Will making sure all the related fields have the same data type make a difference? (Store Number in one table is a double, in another it is a long integer)
    Should I build the relationships between the tables? (currently the only relationships are in the queries)
    Which works better, Make Table queries or a combination of Delete and Append to rebuild the data?



    Thanks for any help with this tuning.
    Richard

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    This is a great resource on this subject.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, I will check it out.
    Richard

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In addition to John's great resource, most of the things you suggest would be appropriate. All tables should have a primary key at a minimum. And you want to have indexes on fields that use a lookup table if you are trying to filter and such values. Note that Access does add some indexes without telling you, so check before you apply indexes in addition to the primary key. Finally, when you say Access hangs, does it literally stop working and you have to kill it with task manager, or does it take many minutes to complete the function?
    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
  •