Results 1 to 13 of 13
  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

    Index anomaly (Access 2000)

    Background: I have 2 tables (let's call them tblParent and tblChild) with enforced RI on the 2 common fields. The child table has about 115,000 records in it. We have a delete query that was deleting records from tblParent for which there were no records in tblChild. It was taking about 2 minutes on a very fast computer to delete just 30 records in the parent. We added a new index on tblChild that indexed these 2 fields, and the delete then took virtually no time at all.

    The problem is that this should not be necessary! When you create a relationship between 2 tables and enforce RI, Access creates a hidden index on the child. Access should be able to use this index to optimize queries.

    Access names this new index by combining the 2 table names, so in the case above it should have been "tblParenttblChild". Using my Documentor utility, I found that the index name wasn't what was expected, but rather it was some sort of system-generated name, probably some combination of table addresses or something. So, I deleted that new index we had created and deleted and then recreated the relationship between the tables. The delete query then ran virtually instantaneously, and when I checked the hidden index, it was again named "tblParenttblChild".

    After doing some more experimenting, I found that importing tables and relationships causes the hidden index names to be changed! Although the table integrity is still maintained, apparently Access can no longer use this index for other situations. This can have a serious impact on performance, as we noted. And since importing all tables into a fresh database is one of the standard procedures for clearing-up problems, it is probable that everyone has this problem! How much of a problem it is will depend on many factors, including table size.

    For my own part, if I have to import tables, I will NOT import the relationships. Even though it is a pain, I will manually recreate them.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Index anomaly (Access 2000)

    I can only say that depending on the hidden indexes is risky, Mark. One of the reasons is the one you tripped over, the fact that you can wind up with system generated GUIDs as relationship names. You're better off creating the specific indexes you want to use, which will retain their given names when you import the tables.
    Charlotte

  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: Index anomaly (Access 2000)

    I don't rely on them in the sense that I'm using Seek and expecting a specific Index name to be there. But I would expect that Access itself should use them properly to optimize queries. The alternative is that for every relationship you have with enforced RI, you have to manually create a new index on the Child table. To me, this is an unacceptable overhead cost.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Index anomaly (Access 2000)

    But aren't the common fields indexed already? You need a unique key to enforce referential integrity.
    Charlotte

  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: Index anomaly (Access 2000)

    The common fields are the PrimaryKey of the Parent, and of course are then unique. They aren't unique in the Child, which is I may not have made clear is what I was talking about.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Index anomaly (Access 2000)

    Well no, I realized they probably weren't unique in the child; but are you saying that you *don't* index the fields in the child table before creating the relationship? I guess it just never occurred to me to do that, since I generally wind up using that index in other ways. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

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

    Re: Index anomaly (Access 2000)

    That's correct, I don't create any indexes in the child that would duplicate the index created by Access when I create the relationship and enforce RI. This reduces system overhead, and also helps me keep below the total of 32 indexes & relationships that a single table can have (every once in a while, this is a problem).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Index anomaly (Access 2000)

    I never even come close to 32 indexes on a table, so I haven't run into the problem. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

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

    Re: Index anomaly (Access 2000)

    It is not just 32 indexes. It is the TOTAL of indexes and relationships which can't exceed 32. So if you have a Master table that is the "one" in 15 one-to-many relationships (with enforced RI), then you only have 17 indexes available. Maybe you have 5 lookup tables (like for Status, Type, Category, Dept., etc.). If RI is enforced, then Access created an Index on your Master table for each of these, bringing you down to 12. If you created your own index on each of these fields, you are down to 7 available indexes. So now you have your PK and perhaps indexes on firstname, lastname, City, State, Zip. Leaves you 1 index to play with. Granted, it is not common you would run out of indexes & relationships; but there is usually one table in your application that is sort of the Master table, which has alot of indexes and relationships. I bet you are closer to that 32 total than you think.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Index anomaly (Access 2000)

    Actually, I tend to delete the MS created indexes. I only want indexes that I specifically create.
    Charlotte

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

    Re: Index anomaly (Access 2000)

    In Tools | Options, there is an option for "AutoIndex on Import/Create". I made this blank, so Access isn't automatically creating any indexes for me when I create new fields like "CustID".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Index anomaly (Access 2000)

    I see that your submission concerning this anomaly got published in the May 2004 issue of Access-VB-SQL Advisor Magazine (in "Advisor Tips"). Congratulations - I hope at least you got the free, highly-coveted "I Tipped Advisor" t-shirt for your efforts!

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

    Re: Index anomaly (Access 2000)

    Mark,

    Thanks! I imagine I will be getting a T-shirt. I''ve already got several; but these are still special, and the only free T-shirts my wife doesn't get first crack at!
    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
  •