"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
