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

    I solved my problem, but... (Access2003)

    I have a client who was experience an very long delay (in excess of 1 minute) when opening a particular form. The form had 2 listboxes on it, which were both Totals queries that essentially pulled info from same 4 tables. These tables are (and relationships, all with enforced RI):

    Customers ----> Orders ----> Work Orders <---- Styles

    The Work Orders table is the central table, and has 300,000+ records in it. The grouping fields for the queries are all indexed fields. There are 6 or so yes/no fields with WHERE conditions; at least 1 from each of the 4 tables. I played with the query, eliminating fields, etc., trying to find what was the big bottleneck.

    When I eliminated the Yes/No field from the Styles table, the query ran in 2 seconds! On a whim, I indexed this field and put the selection back in the query, and it again ran in 2 seconds.

    This makes no sense to me! This table has maybe 20 records in it. The yes/no fields in the other tables (with many more records in them) are not indexed. So why would indexing this one field in this little table make such a huge difference in performance? I guess it has something to do with how Access optimizes the query, but I don't understand how or why. And how do I predict this in future?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: I solved my problem, but... (Access2003)

    That's mysterious, I wouldn't have expected that an index on a field in a table with 20 records would make any difference.

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

    Re: I solved my problem, but... (Access2003)

    Yeah, it just doesn't seem logical.

    I'm wondering if there is a glitch in the optimization routine or something. I think perhaps I'll play with the joins and see if that has any affect.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: I solved my problem, but... (Access2003)

    Have you read this ?
    Regards
    John



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

    Re: I solved my problem, but... (Access2003)

    Interesting article, but it has nothing to do with my situation. I was using all Inner Joins.
    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
  •