Index anomaly (Access 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

I never even come close to 32 indexes on a table, so I haven’t run into the problem.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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”.

Reply | Quote

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”.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

I never even come close to 32 indexes on a table, so I haven’t run into the problem.

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote