By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,661 Members | 1,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,661 IT Pros & Developers. It's quick & easy.

How to get around too many indexes??

P: n/a
This is the first time that I remember ever having too many indexes on a
table, but it has happened.

I have en employees table and store in many places, on tables, the id of the
employee that performed some action. Yes, I know, that could be in an audit
trail but it isn't. For example, who printed a sales order, who processed it
etc is stored on the sales orders table.

Well, I have run out of indexes on the employees table when trying to set up
relationships. For now I have take indexes off of things like employee
postcode fields as they are not really needed, and have not set some
relationships where I am happy that they are not needed - but it bugs me
that I have broken my own rule about relationships.

Has anyone else come across this and how have you handled it?

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au
Aug 9 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
After eliminating "low-priority" indices, next step is a subclass table --
essentially, a one-to-one related table that is an "extension" of the
original table -- thus allowing more indices to be available -- at the cost
of having to use a join in queries.

--

Ken Snell
<MS ACCESS MVP>

"Jeff" <je************@asken.com.auwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
This is the first time that I remember ever having too many indexes on a
table, but it has happened.

I have en employees table and store in many places, on tables, the id of
the employee that performed some action. Yes, I know, that could be in an
audit trail but it isn't. For example, who printed a sales order, who
processed it etc is stored on the sales orders table.

Well, I have run out of indexes on the employees table when trying to set
up relationships. For now I have take indexes off of things like employee
postcode fields as they are not really needed, and have not set some
relationships where I am happy that they are not needed - but it bugs me
that I have broken my own rule about relationships.

Has anyone else come across this and how have you handled it?

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au


Aug 9 '06 #2

P: n/a
Hi Ken

Considered this and it is a real option. Just don't really like having to
create the extra record and maintain it. I know it would only have to
include the employee id. But still a reasonable option.

Jeff

"Ken Snell" <kt***********@ncoomcastt.renaetlwrote in message
news:mP******************************@comcast.com. ..
After eliminating "low-priority" indices, next step is a subclass table --
essentially, a one-to-one related table that is an "extension" of the
original table -- thus allowing more indices to be available -- at the
cost of having to use a join in queries.

--

Ken Snell
<MS ACCESS MVP>

"Jeff" <je************@asken.com.auwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
>This is the first time that I remember ever having too many indexes on a
table, but it has happened.

I have en employees table and store in many places, on tables, the id of
the employee that performed some action. Yes, I know, that could be in an
audit trail but it isn't. For example, who printed a sales order, who
processed it etc is stored on the sales orders table.

Well, I have run out of indexes on the employees table when trying to set
up relationships. For now I have take indexes off of things like employee
postcode fields as they are not really needed, and have not set some
relationships where I am happy that they are not needed - but it bugs me
that I have broken my own rule about relationships.

Has anyone else come across this and how have you handled it?

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au



Aug 9 '06 #3

P: n/a
Jeff, this is a real issue, and probably the Access limit I hit more often
than any other.

Presumably you have already programmatically examined the Indexes collection
to see if there are duplicated indexes. It is quite common to find 2 or 3
identical indexes in a table. For example, if you create a foreign key named
ClientID, Access automatically indexes the field because it is suffixed ID.
Then you manually create an index on the field, so you now have 2. Then you
create a relation between this table and the primary one, with Referential
Integrity of course, and Access creates another hidden index to manage the
constraint. You probably don't use the Lookup Wizard in table design, but it
also creates indexes. So the first step is usually eliminating the multiple
redundant indexes, including any hidden ones.

Once you've done that, and dropped the indexes you can live without, it
sometimes does come down to managing some relationships manually like we
used to do in the old dBase III days.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff" <je************@asken.com.auwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
Hi Ken

Considered this and it is a real option. Just don't really like having to
create the extra record and maintain it. I know it would only have to
include the employee id. But still a reasonable option.

Jeff

"Ken Snell" <kt***********@ncoomcastt.renaetlwrote in message
news:mP******************************@comcast.com. ..
>After eliminating "low-priority" indices, next step is a subclass
table -- essentially, a one-to-one related table that is an "extension"
of the original table -- thus allowing more indices to be available -- at
the cost of having to use a join in queries.

--

Ken Snell
<MS ACCESS MVP>

"Jeff" <je************@asken.com.auwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
>>This is the first time that I remember ever having too many indexes on a
table, but it has happened.

I have en employees table and store in many places, on tables, the id of
the employee that performed some action. Yes, I know, that could be in
an audit trail but it isn't. For example, who printed a sales order, who
processed it etc is stored on the sales orders table.

Well, I have run out of indexes on the employees table when trying to
set up relationships. For now I have take indexes off of things like
employee postcode fields as they are not really needed, and have not set
some relationships where I am happy that they are not needed - but it
bugs me that I have broken my own rule about relationships.

Aug 9 '06 #4

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:44***********************@per-qv1-newsreader-01.iinet.net.au:
Jeff, this is a real issue, and probably the Access limit I hit more
often than any other.
There are many good reasons for assessing need before creating indexes.

From Microsoft® Jet Database Engine Programmer’s Guide

"Use Indexes Cautiously
Although having indexes can reduce the time it takes to retrieve data,
they always carry a cost in maintenance and concurrency issues. When
should a field be indexed? There is no strict answer for this because it
depends on the type of application.

The first guideline is that fields containing highly duplicated data
should not be indexed (for example, fields with the Yes/No data type, and
fields that represent gender, state abbreviations, or country codes).

The second guideline is that fields should not be indexed simply to force
Rushmore to use more than one index. An example of this would be indexing
a field called City and a field called PostalCode in a customer table
when the application is always going to be using both fields for
retrieval purposes. In this instance, the PostalCode field is going to be
the most unique index and would return a result set faster if the City
field was not indexed. Because Rushmore doesn’t need to use the index on
the City field, omitting the index on the City field will reduce overall
disk I/O. Of course, if both values were not always being entered and
they were used alternatively and equally, then having an index on both
fields would probably be advantageous. Rushmore is best utilized on
combined indexes when they are required to generate a unique result set.

It is also important to remember that indexes create concurrency issues.
One index page represents many data pages. Therefore, modifying an index
page can cause users with data on an entirely different data page to be
locked out when trying to update the indexed field."
Presumably you have already programmatically examined the Indexes
collection to see if there are duplicated indexes. It is quite common
to find 2 or 3 identical indexes in a table. For example, if you
create a foreign key named ClientID, Access automatically indexes the
field because it is suffixed ID.
This is one of the first defaults that I turn off when using a new
version of Access. Of course, I turn off as many Access defaults as
possible, on the general principle that Access defaults are likely to be
resource wasteful, inefficient and to cause arcane problems.

Then you manually create an index on
the field, so you now have 2. Then you create a relation between this
table and the primary one, with Referential Integrity of course, and
Access creates another hidden index to manage the constraint.
What are permanent relationships without referential integiry for?
probably don't use the Lookup Wizard in table design, but it also
creates indexes.
There are no wizards. There is only a collection of evil witches
masquerading as wizards.
Once you've done that, and dropped the indexes you can live without,
it sometimes does come down to managing some relationships manually
like we used to do in the old dBase III days.
Back to the one-to-one relationships; if excess number of tables is not
an issue we can have a separate table for each (normal) column with two
fields, id and data fields. It's unlikely we will have too many indexes
here.
And of course we can manage the associations of those single field(plus
id) tables with linking tables and very powerful views. (If we use any
flavor of MS-SQl we can index the views although this capability does not
yet seem to be perfect).
IMO this will or should be the future of databases.

--
Lyle Fairfield
Aug 9 '06 #5

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:44***********************@per-qv1-newsreader-01.iinet.net.au:
Once you've done that, and dropped the indexes you can live
without, it sometimes does come down to managing some
relationships manually like we used to do in the old dBase III
days.
Could one get round the limitation by splitting a single table into
two 1:1 tables?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 9 '06 #6

P: n/a
* David W. Fenton:
"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:44***********************@per-qv1-newsreader-01.iinet.net.au:
>Once you've done that, and dropped the indexes you can live
without, it sometimes does come down to managing some
relationships manually like we used to do in the old dBase III
days.

Could one get round the limitation by splitting a single table into
two 1:1 tables?
This option was suggested by Ken Snell earlier in the thread.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Aug 9 '06 #7

P: n/a
Hi Allen

Basically I have done all that. The majority of these indexes are due to
relationships. It does look like I will have to manage a few manually.

Jeff

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
Jeff, this is a real issue, and probably the Access limit I hit more often
than any other.

Presumably you have already programmatically examined the Indexes
collection to see if there are duplicated indexes. It is quite common to
find 2 or 3 identical indexes in a table. For example, if you create a
foreign key named ClientID, Access automatically indexes the field because
it is suffixed ID. Then you manually create an index on the field, so you
now have 2. Then you create a relation between this table and the primary
one, with Referential Integrity of course, and Access creates another
hidden index to manage the constraint. You probably don't use the Lookup
Wizard in table design, but it also creates indexes. So the first step is
usually eliminating the multiple redundant indexes, including any hidden
ones.

Once you've done that, and dropped the indexes you can live without, it
sometimes does come down to managing some relationships manually like we
used to do in the old dBase III days.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff" <je************@asken.com.auwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
>Hi Ken

Considered this and it is a real option. Just don't really like having to
create the extra record and maintain it. I know it would only have to
include the employee id. But still a reasonable option.

Jeff

"Ken Snell" <kt***********@ncoomcastt.renaetlwrote in message
news:mP******************************@comcast.com ...
>>After eliminating "low-priority" indices, next step is a subclass
table -- essentially, a one-to-one related table that is an "extension"
of the original table -- thus allowing more indices to be available --
at the cost of having to use a join in queries.

--

Ken Snell
<MS ACCESS MVP>

"Jeff" <je************@asken.com.auwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...
This is the first time that I remember ever having too many indexes on
a table, but it has happened.

I have en employees table and store in many places, on tables, the id
of the employee that performed some action. Yes, I know, that could be
in an audit trail but it isn't. For example, who printed a sales order,
who processed it etc is stored on the sales orders table.

Well, I have run out of indexes on the employees table when trying to
set up relationships. For now I have take indexes off of things like
employee postcode fields as they are not really needed, and have not
set some relationships where I am happy that they are not needed - but
it bugs me that I have broken my own rule about relationships.


Aug 10 '06 #8

P: n/a
Jeff wrote:
Hi Allen

Basically I have done all that. The majority of these indexes are due to
relationships. It does look like I will have to manage a few manually.
How is a relationship managed manually?

Aug 11 '06 #9

P: n/a
What I meant by that, Lyle, is that I--as the developer--take on
responsibility for managing the relationship, as distinct from allowing the
engine to manage it.

But I suspect you knew that. :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11*********************@74g2000cwt.googlegrou ps.com...
Jeff wrote:
>Hi Allen

Basically I have done all that. The majority of these indexes are due to
relationships. It does look like I will have to manage a few manually.

How is a relationship managed manually?

Aug 11 '06 #10

P: n/a
Allen Browne wrote:
What I meant by that, Lyle, is that I--as the developer--take on
responsibility for managing the relationship, as distinct from allowing the
engine to manage it.

But I suspect you knew that. :-)
I was afraid that's what you meant. On Foxpro, as you know, this is how
it had to be done, at least originally.
I had a school-class-enrolment-staff application going and I thought
I--as the devloper--had it battened down tight. What to my wondering
eyes should appear but the total of classes coming out as 22 more than
actually showed. What could have happened? Well, a young attractive
lady asked the system administrator to delete a school for her. He
didn't know how to use the application but with his password he had
access to everything so, needing to appear omnipotent in her eyes, he
went directly to the table of schools and deleted the school (not from
within the application). But he didn't delete the school's classes.
Since no one, at the time, knew this, it was the guy who --as developer
-- was managing the relationship who looked bad (me). A year or so
later, having got a great new job at IBM, he confessed to me what
happened in his last few days. (I then confessed to him that I was the
person who had duplicated Stella Woo's employment record and created
her twin sister, Deja, after the Supreme Commander of Computers had
told me it was impossible.)

Nowadays, even in Access, even with RI, I still run CheckforOrphans
(well I check for a bunch of things) code before the application
prints final reports. If the 22 classes had been counted, the Board by
the terms of the Collective Agreement with the Teachers would have
been required to hire an extra 22 teachers at about $65000 each. I
think that's almost 1.5 million.

RI was the first reason I decided to move to JET and Access.

Aug 11 '06 #11

P: n/a
Actually, I meant that whenever the customer wants to delete something I
would travel into his office and delete all the records
'manually'.............just kidding.

Jeff

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11*********************@74g2000cwt.googlegrou ps.com...
Jeff wrote:
>Hi Allen

Basically I have done all that. The majority of these indexes are due to
relationships. It does look like I will have to manage a few manually.

How is a relationship managed manually?

Aug 11 '06 #12

P: n/a
Jeff wrote:
Actually, I meant that whenever the customer wants to delete something I
would travel into his office and delete all the records
'manually'.............just kidding.
A welding torch played along the hard dirve is a good way to do this.
While you're there remember that sometimes the customer can benefit
from a brief application to his ass, as well!

Aug 11 '06 #13

P: n/a
"Lyle Fairfield" <ly***********@aim.comwrote
If the 22 classes had been counted, the Board by
the terms of the Collective Agreement with the
Teachers would have been required to hire an
extra 22 teachers at about $65000 each. I
think that's almost 1.5 million.
I read of some people who took a different approach... they just created
imaginary teachers to receive the paychecks, and it actually took quite some
time for them to be caught. If they'd only absconded a little sooner to some
jurisdiction with no workable extradition treaty...

Larry
Aug 11 '06 #14

P: n/a
I get the feeling you have had a bad experience with a client recently.
Maybe a little meditation would be a good thing.

Jeff

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11*********************@i42g2000cwa.googlegro ups.com...
Jeff wrote:
>Actually, I meant that whenever the customer wants to delete something I
would travel into his office and delete all the records
'manually'.............just kidding.

A welding torch played along the hard dirve is a good way to do this.
While you're there remember that sometimes the customer can benefit
from a brief application to his ass, as well!

Aug 13 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.