Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to speed up query (97)

    how to speed up query
    I created a query that takes a LONG time to load 10-15mins & I was looking for some helpful tips on how to possibly make it load faster? Please advise.

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

    Re: how to speed up query (97)

    The most important thing is indexes. Any field that is used in the ORDER BY or the WHERE clauses should be indexed. If Referential Integrity has been set between the affect tables, then they are alreadying indexed on the connecting fields (these are hidden indexes).

    Is there anything else about this particular query that makes it unusual? That is, it it simply just a query with several tables, or is it a cross-tab query, or a query based on a query, etc.?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to speed up query (97)

    well, while my tables are indexed, that index is not what I

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

    Re: how to speed up query (97)

    Access uses its indexes to reduce the number of records it has to read. For example, take this simple clause "... WHERE State='FL'...". If State is not indexed, then Access has to read EVERY record in the table. If State is indexed, then Access can go fairly quickly to the first 'FL' record, and then read all the 'FL' records, then its done.

    When you base a query upon a query, then Access has to resolve the underlying query first. If possible, try to put your selection criteria in this first query, so you are passing fewer records on to the second query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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