Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Improving Performance (Access 2003/SP3)

    A SQL geek suggested to me that I index the Access db to improve performance. I believe that index must mean something different in his world than mine.
    My current to do list looks like this:
    turn off Name autocorrect
    Remove autoindexing
    Remove subdatasheets
    remove excess indexes

    Have I missed anything?

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

    Re: Improving Performance (Access 2003/SP3)

    In the first place, indexing a database means creating indexes in tables.

    It's a matter of balance - some indexes are required, some are useful and others are superfluous.

    1) You need an index on the join field(s) if you have a relationship between two tables with enforced Referential Integrity - if you haven't created a primary key on the join field(s) on the "one" side, you can't enforce referential integrity, and if you haven't created an index on the join fields on the "many" side, Access will create an invisible one.

    2) If you frequently sort a table on a field, or frequently search within a field, an index on this field will improve performance, especially if the number of records is large. In a table with 30 records, you won't notice the effect of the absence or presence of an index, but in a table with many thousands of records you may notice an improvement.

    3) An index takes up space and it takes time to keep it up-to-date, so unnecessary indexes have a negative impact on performance. That is the rationale behind the tips to remove auto-indexing and to remove excess indexes.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Improving Performance (Access 2003/SP3)

    So, as this db does have indexes. I can conclude he was talking through his hat. I just thought there was some mysterious SQL function that I could apply...

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

    Re: Improving Performance (Access 2003/SP3)

    It isn't unusual for SQL Server "guys" to offer advice that doesn't quite fit Access if they aren't intimately familiar with the Jet databse engine. The assumption is that Access works like SQL Server and other database servers. That presumption quite often leads to rude surprises for the "guy" in question. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Charlotte

Posting Permissions

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