Too many indexes when creating relationship

[Guest]

I have a principal table in this particular DB that requires referential integrity on about 20 fields. At 16, I run into the problem of "too many indexes" when attempting to create the next relationship.

What problems, if any, could potentially arise from removal of indexes from fields (i.e. table design view -> field properties -> change Indexed to "No") that have previously-established cascade-update relationships with other tables?

 

[Marshall Barton]

I have a principal table in this particular DB that requires referential integrity on about 20 fields. At 16, I run into the problem of "too many indexes" when attempting to create the next relationship.

What problems, if any, could potentially arise from removal of indexes from fields (i.e. table design view -> field properties -> change Indexed to "No") that have previously-established cascade-update relationships with other tables?

Generally, when you create a relationship Access
automatically creates the indexes it needs (whether you
already created them or not). This frequently results in
two sets of indexes and you hit the limit of 32 indexes, but
you can see 16 of them in you table design view. Note that
referential integrity is specified in the relationships
window, not in table design.

If you create a procedure to loop through the table def's
Indexes collection and use Debug.Print to display them in
the immediate window, you can see this happening and verify
that you don't need to specify the indexes in the table's
design.

 

[david epsom dot com dot au]

What problems, if any, could potentially arise from removal of indexes
from fields (i.e. table design view -> field properties -> change Indexed to
"No") that have previously-established cascade-update relationships with
other tables?

queries/views will still be updatable, but may run slower.

(david)


integrity on about 20 fields. At 16, I run into the problem of "too many
indexes" when attempting to create the next relationship.from fields (i.e. table design view -> field properties -> change Indexed to
"No") that have previously-established cascade-update relationships with
other tables?

 

[Marshall Barton]

David, I didn't think that would happen if the fields were
involved in a relation. Wouldn't a query just use the
hidden indexes (i.e. a query can't tell how an index was
created)??

While we're on this topic, you wouldn't happen to know of a
way to change the name of these hidden indexes, would you?

 

[david epsom dot com dot au]

I saw a report recently that removing a named index (a
single field index) on a related field made a query run
slower. The guy who reported it was intrigued too. I
can't find the reference. I can speculate on several
reasons why it might be so, so I don't discount the report.
(But you're getting it at second hand now, so you should
discount it at least a little bit!)

I can't remember if we ever tried to change the name of
a Foreign Key index, and it's been a long time since I
was working on (DAO stuff) to do that kind of thing.
sorry...

(david)

David, I didn't think that would happen if the fields were
involved in a relation. Wouldn't a query just use the
hidden indexes (i.e. a query can't tell how an index was
created)??

While we're on this topic, you wouldn't happen to know of a
way to change the name of these hidden indexes, would you?
--
Marsh
MVP [MS Access]

from fields (i.e. table design view -> field properties -> change Indexed to
"No") that have previously-established cascade-update relationships with
other tables?

queries/views will still be updatable, but may run slower.

(david)

referential
integrity on about 20 fields. At 16, I run into the problem of "too many
indexes" when attempting to create the next relationship.
from fields (i.e. table design view -> field properties -> change Indexed to
"No") that have previously-established cascade-update relationships with
other tables?

 

[Marshall Barton]

I saw a report recently that removing a named index (a
single field index) on a related field made a query run
slower. The guy who reported it was intrigued too. I
can't find the reference. I can speculate on several
reasons why it might be so, so I don't discount the report.
(But you're getting it at second hand now, so you should
discount it at least a little bit!)

Are you sure there was also a relation using that field? It
would surprise me if there were.

I can't remember if we ever tried to change the name of
a Foreign Key index, and it's been a long time since I
was working on (DAO stuff) to do that kind of thing.
sorry...

Oh well. It was just a shot in the dark, so no worries.
Thanks for the response.
--
Marsh
MVP [MS Access]

David, I didn't think that would happen if the fields were
involved in a relation. Wouldn't a query just use the
hidden indexes (i.e. a query can't tell how an index was
created)??

While we're on this topic, you wouldn't happen to know of a
way to change the name of these hidden indexes, would you?
--
Marsh
MVP [MS Access]

What problems, if any, could potentially arise from removal of indexes
from fields (i.e. table design view -> field properties -> change Indexed to
"No") that have previously-established cascade-update relationships with
other tables?

queries/views will still be updatable, but may run slower.

(david)


Brian wrote:
I have a principal table in this particular DB that requires referential
integrity on about 20 fields. At 16, I run into the problem of "too many
indexes" when attempting to create the next relationship.

What problems, if any, could potentially arise from removal of indexes
from fields (i.e. table design view -> field properties -> change Indexed to
"No") that have previously-established cascade-update relationships with
other tables?


"Marshall Barton" wrote
Generally, when you create a relationship Access
automatically creates the indexes it needs (whether you
already created them or not). This frequently results in
two sets of indexes and you hit the limit of 32 indexes, but
you can see 16 of them in you table design view. Note that
referential integrity is specified in the relationships
window, not in table design.

If you create a procedure to loop through the table def's
Indexes collection and use Debug.Print to display them in
the immediate window, you can see this happening and verify
that you don't need to specify the indexes in the table's
design.

 

[david epsom dot com dot au]

Are you sure there was also a relation using that field? It

would surprise me if there were.

Yes, my memory is that it was an explicit report:
"I have a simple relationship between two fields, and
to my surprise, when I removed the extra index, a
query ran much more slowly. It looks like Jet treats
an index that has the right name differently than
the indexes created by a relationship"

I can't find the report now, so we are at the mercy
of my (fallible) memory -- or we could do more testing....

but I'm comfortable with the rather vague :~) advice
I gave: removing a duplicate index on a field probably
won't break any thing, but /may/ make something run
slower :~)

(david)

I saw a report recently that removing a named index (a
single field index) on a related field made a query run
slower. The guy who reported it was intrigued too. I
can't find the reference. I can speculate on several
reasons why it might be so, so I don't discount the report.
(But you're getting it at second hand now, so you should
discount it at least a little bit!)

Are you sure there was also a relation using that field? It
would surprise me if there were.

I can't remember if we ever tried to change the name of
a Foreign Key index, and it's been a long time since I
was working on (DAO stuff) to do that kind of thing.
sorry...

Oh well. It was just a shot in the dark, so no worries.
Thanks for the response.
--
Marsh
MVP [MS Access]

David, I didn't think that would happen if the fields were
involved in a relation. Wouldn't a query just use the
hidden indexes (i.e. a query can't tell how an index was
created)??

While we're on this topic, you wouldn't happen to know of a
way to change the name of these hidden indexes, would you?
--
Marsh
MVP [MS Access]



david epsom dot com dot au wrote:

What problems, if any, could potentially arise from removal of indexes
from fields (i.e. table design view -> field properties -> change

Indexed
to

"No") that have previously-established cascade-update relationships with
other tables?

queries/views will still be updatable, but may run slower.

(david)


Brian wrote:
I have a principal table in this particular DB that requires referential
integrity on about 20 fields. At 16, I run into the problem of "too many
indexes" when attempting to create the next relationship.

What problems, if any, could potentially arise from removal of indexes
from fields (i.e. table design view -> field properties -> change

Indexed
to

"No") that have previously-established cascade-update relationships with
other tables?


"Marshall Barton" wrote
Generally, when you create a relationship Access
automatically creates the indexes it needs (whether you
already created them or not). This frequently results in
two sets of indexes and you hit the limit of 32 indexes, but
you can see 16 of them in you table design view. Note that
referential integrity is specified in the relationships
window, not in table design.

If you create a procedure to loop through the table def's
Indexes collection and use Debug.Print to display them in
the immediate window, you can see this happening and verify
that you don't need to specify the indexes in the table's
design.

 

[Marshall Barton]

Yes, my memory is that it was an explicit report:
"I have a simple relationship between two fields, and
to my surprise, when I removed the extra index, a
query ran much more slowly. It looks like Jet treats
an index that has the right name differently than
the indexes created by a relationship"

I can't find the report now, so we are at the mercy
of my (fallible) memory -- or we could do more testing....

but I'm comfortable with the rather vague :~) advice
I gave: removing a duplicate index on a field probably
won't break any thing, but /may/ make something run
slower :~)

Interesting, now I've got to keep my eyes open for
what/when/where/why might be causing it. First thought to
jump to mind is that the query (plan?) may have been been
saved using the deleted index and resaving the query (or
compacting) might get it back to its original speed?? Not
even sure that makes sense, scratching head :-\