Index Question

[Matt]

Hi All,

I am creating a new database and am very familiar with the concept of
indexing and its benefits, but I had some questions on which fields to
index.

Is there a general guideling or set of circumstances other than the
following that should determine if a field is indexed:
-The field is a primary key
-The field is a foriegn key
-The field will be part of a where clause


For instance, I the following table is part of the database:

tblProductivity
----------------------------------------------------------------------------------
PRODUCTIVITY_PK: autonumber; Primary Key; indexed
PRODUCTIVE_DATE: date/time(mm/dd/yyyy);indexed
AWD_USER_GROUP: text
USER_ID_FK: text, indexed
TEAM_FK: Number; indexed
SUB_TEAM_1_FK: Number; indexed
UNIT_FK: Number; indexed
CATEGORY_FK: Number; indexed
WORKTYPE: Text;
STATUS: Text;
COUNT: Number
ELAPSED_TIME: double
AWD_WORK: Yes/No; Indexed

I have indexed all of the foriegn keys and the date (the dats is used
in many joins and quite often in where clauses).
The WORKTYPE and STATUS are also used in quite a few where clauses,
and sometimes in joins.


Should I index them?
Does indexing too many fields in one table reduce its effectiveness?

Any other information is greatly appreciated!

As always, Thanks in advance.

 

[Allen Browne]

A fourth reason for indexing a field is if you will sort on it regularly.

Assuming that you create relationships with RI enforced, you do not need to
index your foreign key fields. Access will create indexes on them anyway, so
as to reinforce the integrity.

Conventional wisdom is not to index fields that have few choices, such as
yes/no fields. But my timing tests in an Access table with 15k client
records suggests that there's significant benefit in doing so if you are
always selecting based on this field (e.g. you mostly want just the active
clients.)

Once you reach the stage where you know most of the fields of a table to
index and most not to, but there's a few you have trouble deciding on, you
might consider how static the data is. If data is constantly being inserted,
deleted, and modified, then it might not be a good idea to use lots of
indexes: the effort JET must expend to maintain them could more than offset
the benefit the indexes give for retrieving data. Conversely, if the data
rarely changes, it makes sense to index the questionable fields for the
times when you need them, as there is little overhead beyond actually
loading the indexes.

The other factor is the number of records in the table. For a small table
(just a few hundred records), the table scan might be nearly as good as the
index, so the overhead of maintaining the index is unjustified. (Personally
I find it hard to obey this rule, because you never know how many records a
table will ultimately contain.)

 

[Matt]

A fourth reason for indexing a field is if you will sort on it regularly.

Assuming that you create relationships with RI enforced, you do not need to
index your foreign key fields. Access will create indexes on them anyway,so
as to reinforce the integrity.

Conventional wisdom is not to index fields that have few choices, such as
yes/no fields. But my timing tests in an Access table with 15k client
records suggests that there's significant benefit in doing so if you are
always selecting based on this field (e.g. you mostly want just the active
clients.)

Once you reach the stage where you know most of the fields of a table to
index and most not to, but there's a few you have trouble deciding on, you
might consider how static the data is. If data is constantly being inserted,
deleted, and modified, then it might not be a good idea to use lots of
indexes: the effort JET must expend to maintain them could more than offset
the benefit the indexes give for retrieving data. Conversely, if the data
rarely changes, it makes sense to index the questionable fields for the
times when you need them, as there is little overhead beyond actually
loading the indexes.

The other factor is the number of records in the table. For a small table
(just a few hundred records), the table scan might be nearly as good as the
index, so the overhead of maintaining the index is unjustified. (Personally
I find it hard to obey this rule, because you never know how many recordsa
table will ultimately contain.)

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














- Show quoted text -

The table today consists of 600,000 records and growing. We are in
the process of upsizing to SQL server as a Backend and are redesigning
some of the poorly designed tables currently in the Access BE.

The data is appended from an ODBC source overnight, so update
performance would fall to the bottom of my concern list, unless of
course this could affect the actual appended data.

 

[dbahooker]

yes there is a consensus

don't worry about indexing
move to SQL Server

use SQL Profiler and Index Tuning Wizard / Database Tuning Advisor

it's a lot easier / better this way; sorry

-Aaron

 

[dbahooker]

dude don't try to keep 60,000 records in Access

keep it in SQL Server and use Access Data Proejcts
it is a lot lot lot simpler

 

[Aaron Kempf]

Access doesn't support that many records

you should keep your data in SQL Server and use Access Data Projects

then, it is much simpler and better to use SQL Profiler, combined with
either 'Index Tuning Wizard' or 'Database Tuning Advisor'

Access MDB isn't strong enough for a single record and a single user

A fourth reason for indexing a field is if you will sort on it regularly.

Assuming that you create relationships with RI enforced, you do not need to
index your foreign key fields. Access will create indexes on them anyway, so
as to reinforce the integrity.

Conventional wisdom is not to index fields that have few choices, such as
yes/no fields. But my timing tests in an Access table with 15k client
records suggests that there's significant benefit in doing so if you are
always selecting based on this field (e.g. you mostly want just the active
clients.)

Once you reach the stage where you know most of the fields of a table to
index and most not to, but there's a few you have trouble deciding on, you
might consider how static the data is. If data is constantly being inserted,
deleted, and modified, then it might not be a good idea to use lots of
indexes: the effort JET must expend to maintain them could more than offset
the benefit the indexes give for retrieving data. Conversely, if the data
rarely changes, it makes sense to index the questionable fields for the
times when you need them, as there is little overhead beyond actually
loading the indexes.

The other factor is the number of records in the table. For a small table
(just a few hundred records), the table scan might be nearly as good as the
index, so the overhead of maintaining the index is unjustified. (Personally
I find it hard to obey this rule, because you never know how many records a
table will ultimately contain.)

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













- Show quoted text -

The table today consists of 600,000 records and growing. We are in
the process of upsizing to SQL server as a Backend and are redesigning
some of the poorly designed tables currently in the Access BE.

The data is appended from an ODBC source overnight, so update
performance would fall to the bottom of my concern list, unless of
course this could affect the actual appended data.

 

[Pat Hartman \(MVP\)]

Neither Access nor other relational database engines will use an index on a
field that has only limited non-unique values. For example an index on a
field containing only the two values Female/Male will never be used. If I
want to create an index on such a field, I add the autonumber as the second
field so the index entry actually points to a unique record.

A fourth reason for indexing a field is if you will sort on it regularly.

Assuming that you create relationships with RI enforced, you do not need
to
index your foreign key fields. Access will create indexes on them anyway,
so
as to reinforce the integrity.

Conventional wisdom is not to index fields that have few choices, such as
yes/no fields. But my timing tests in an Access table with 15k client
records suggests that there's significant benefit in doing so if you are
always selecting based on this field (e.g. you mostly want just the active
clients.)

Once you reach the stage where you know most of the fields of a table to
index and most not to, but there's a few you have trouble deciding on, you
might consider how static the data is. If data is constantly being
inserted,
deleted, and modified, then it might not be a good idea to use lots of
indexes: the effort JET must expend to maintain them could more than
offset
the benefit the indexes give for retrieving data. Conversely, if the data
rarely changes, it makes sense to index the questionable fields for the
times when you need them, as there is little overhead beyond actually
loading the indexes.

The other factor is the number of records in the table. For a small table
(just a few hundred records), the table scan might be nearly as good as
the
index, so the overhead of maintaining the index is unjustified.
(Personally
I find it hard to obey this rule, because you never know how many records
a
table will ultimately contain.)

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














- Show quoted text -

The table today consists of 600,000 records and growing. We are in
the process of upsizing to SQL server as a Backend and are redesigning
some of the poorly designed tables currently in the Access BE.

The data is appended from an ODBC source overnight, so update
performance would fall to the bottom of my concern list, unless of
course this could affect the actual appended data.

 

[dbahooker]

you should be using BITWISE Operators in this situation

from what I understand-- these can be seeked on an index quite nicely

I love bitwise operators

SOOOOO much better than having a half dozen yes/no fields

 

[dbahooker]

you need to move to Access Data Projects, which utilize SQL Server

Access MDB will never work well with this many records-- even if you
move to a SQL Server backend; MDB will still scan the whole table
across the network


Access MDB sucks balls
move to SQL Server and Access Data Projects

it is a TON easier to deal with

 

[Allen Browne]

Interesting. I never do that, Pat.

The particular case I was referring to was a table of clients, with a yes/no
field named Inactive. Since we almost always wanted just the active clients,
I considered indexing this field, even though it had only 2 values.
Conventional wisdom said don't, but I ran some tests anyway. JET retrieved
the fields almost an order of magnitude faster with the index on the yes/no
field.

There was a separate AutoNumber primary key, but it was not part of this
index. This was more than 10 years ago, but I assume the current versions do
the same.

 

[Tony Toews [MVP]]

Neither Access nor other relational database engines will use an index on a
field that has only limited non-unique values. For example an index on a
field containing only the two values Female/Male will never be used. If I
want to create an index on such a field, I add the autonumber as the second
field so the index entry actually points to a unique record.

I'm not at all sure about this. David Fenton once created an index on
a Yes/No field and stated he had a dramatic performance improvement on
a query which was using that particular field.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Pat Hartman \(MVP\)]

I stand corrected. Although, I would still like to see the execution plan
that says that Jet is going to use the index. How many rows were in the
table? The optimizer analyzes reads with and without indexes and if the
index does not provide sufficient segregation, it will not be used. I
probably shouldn't have said never. Unlikely would be more like it.

 

[Tony Toews [MVP]]

I stand corrected. Although, I would still like to see the execution plan
that says that Jet is going to use the index. How many rows were in the
table? The optimizer analyzes reads with and without indexes and if the
index does not provide sufficient segregation, it will not be used. I
probably shouldn't have said never. Unlikely would be more like it.

You'll have to discuss this with David Fenton. I have no idea as to any more details
and I may have incorrectly stated a few things.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Allen Browne]

Tony, David may have reported that, but I certainly have.

Table of 15k clients.
Yes/No field for marking records inactive.
Almost all queries pull just the active clients.
Something like an order of magnitude faster.
Measured back in A97 days.

Easy enough for anyone to set up their own tests.

 

[seroseles]

Assuming that you create relationships with RI enforced, you do not need to
index your foreign key fields. Access will create indexes on them anyway,so
as to reinforce the integrity.

But Access has also «fast foreign key»

A fast foreign key is a foreign key that has no index. Although this
may seem counter-intuitive, there is a valid explanation for it. By
default, when a foreign key is defined, an index based on the
column(s) in the foreign key is created automatically. In many
instances this enhances performance when executing operations that
maintain referential integrity. However, if there are many duplicated
values in the foreign key field, the foreign key index will affect
performance when records are added and deleted from the table. To
prevent the automatic creation of indexes on foreign keys, use the NO
INDEX keywords in the declaration of the foreign key.

Copy Code
ALTER TABLE tblInvoices
ADD CONSTRAINT FK_tblInvoices
FOREIGN KEY NO INDEX (CustomerID) REFERENCES
tblCustomers (CustomerID)

(http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx)

Fast foreign keys
Typically, when a foreign key is defined, an index based on the fields
that make up the foreign key are created automatically. In many
instances, this enhances performance when executing operations that
maintain referential integrity.

However, foreign key indexes can also reduce performance and
concurrency. In cases where the values in a foreign key index are
highly duplicated, using an index can be less efficient than simply
scanning the table. And maintaining such an index as records are
inserted and deleted from the table, can degrade performance even
further.

Also, the locking of index pages decreases concurrency. In other
words, this can increase the likelihood that a user will have to wait
until a lock is released by another user before their query can
continue execution.

Finally, modifying index pages requires that lock requests be placed
over the network. This additional I/O can further degrade performance.

(http://support.microsoft.com/default.aspx?scid=kb;en-us;275561)