Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Speed up performance (2000)

    Is there any way to improve performance for a database on the network? I searched on this and got a few things that I used but I didn't see any improvement. I changed the subdatasheet property to [none] for the tables, I made sure name autocorrect was unchecked. There are approximately 25,000 records in this database. The search forms are very slow - everything take minutes instead of seconds.

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

    Re: Speed up performance (2000)

    Do you have an index on the field(s) you want to search on? With small tables, it won't matter much, but with 25,000 records, an index will speed up searching.

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

    Re: Speed up performance (2000)

    Something else:
    - Make a backup copy of the database (for safety)
    - Decompie the database (see <post#=44865>post 44865</post#> for an easy way to decompile)
    - Then compile the code (Debug | Compile <projectname> in the Visual Basic Editor)
    - Finally compact the database (Tools | Database Utilities | Compact and Repair Database)

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

    Re: Speed up performance (2000)

    And here some other speed up techniques :
    Microsoft Access Performance FAQ
    Francois

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Speed up performance (2000)

    Linda,
    I know this may sound basic, but do you have Macros? Or is everything in VB Code? If you've got macros, I would suggest converting them to VB code.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Speed up performance (2000)

    I'm guessing one of your biggest problems may be lack of indexes on the search/sort fields, as Hans suggested.

    But you really need to be more specific about the situations. For example, if your search form contains multiple combo boxes, each with some 25,000 records in it, then it will take awhile to load. If you standard data entry screen has as its recordsource the entire table, then it will take awhile to load. Or if there are alot of subforms, etc.

    Basically, each time you create a recordset (recordsource for a form/report, rowsource for combo/list boxes) you need to be aware of how many records will be returned, what fields are part of your selection criteria, and what is the sort sequence of the information. 25,000 records in a database really isn't that much, so I'd say there is substantial room for improvement.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Speed up performance (2000)

    You don't indicate whether the database in question is split into a front-end and a back-end. If it isn't, it should be. Also, you want to move the front-end to the workstation for a number of reasons. Also note that there are quite a few performance "myths" that arose with Access 1 and 2 that are no longer issues. Finally, one object is to return as few records as possible, but in Access that is pretty difficult, as the Jet engine always runs on the workstation. So if you are searching across 25000 records, at a minimum Access will want to pull the indexes across the LAN. To solve that problem, you will want to run a true database engine such as SQL Server, Oracle, DB2 or mySQL. We normally use SQL Server, and do queries on tables with million-plus records with subsecond response times. Hope this helps - when I get back into the office next week, I'll try and find a presentation I did on Access performance.
    Wendell

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Speed up performance (2000)

    ...somehow, I sense something along the lines of a starpost (or comparable quality post) coming...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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