Microsoft Access 2010 Bible

(Rick Simeone) #1

Chapter 2: Creating Access Tables


83


A field can be both the primary key for a table and part of a composite index. You should index your
tables as necessary to yield the highest possible performance without worrying about over-indexing
or violating some arcane indexing rules. For example, in a database such as Collectible Mini Cars, the
invoice number in tblSales is frequently used in forms and reports, and should be indexed. In
addition, there are many situations in which the invoice number is used in combinations with other
fields, such as the sales date or salesperson ID. You should consider adding composite indexes com-
bining the invoice number with sales date, and salesperson ID, to the sales table.


When to index tables


Depending on the number of records in a table, the extra overhead of maintaining an index may
not justify creating an index beyond the table’s primary key. Though data retrieval is somewhat
faster than it is without an index, Access must update index information whenever you enter or
change records in the table. In contrast, changes to nonindexed fields do not require extra file
activity. You can retrieve data from nonindexed fields as easily (although not as quickly) as from
indexed fields.


Generally speaking, it’s best to add secondary indexes when tables are quite large, and when index-
ing fields other than the primary key speeds up searches. Even with large tables, however, indexing
can slow performance if the records in tables will be changed often or new records will be added
frequently. Each time a record is changed or added, Access must update all indexes in the table.


Given all the advantages of indexes, why not index everything in the table? What are the draw-
backs of indexing too many fields? Is it possible to over-index tables?


First, indexes increase the size of the Access database somewhat. Unnecessarily indexing a table
that doesn’t really require an index eats up a bit of disk space for each record in the table. More
important, indexes extract a performance hit for each index on the table every time a record is
added to the table. Because Access automatically updates indexes each time a record is added (or
removed), the internal indexing must be adjusted for each new record. If you have ten indexes on
a table, Access makes ten adjustments to the indexes each time a new record is added or an exist-
ing record is deleted, causing a noticeable delay on large tables (particularly on slow computers).


Sometimes changes to the data in records cause adjustments to the indexing scheme. This is true if
the change causes the record to change its position in sorting or query activities. Therefore, if
you’re working with large, constantly changing data sets that are rarely searched, you may choose
not to index the fields in the table, or to minimally index by indexing only those few fields that are
likely to be searched.


As you begin working with Access tables, you’ll probably start with the simplest one-field indexes
and migrate to more complex ones as your familiarity with the process grows. Do keep in mind,
however, the tradeoffs between greater search efficiency and the overhead incurred by maintaining
a large number of indexes on your tables.


It’s also important to keep in mind that indexing does not modify the physical arrangement of
records in the table. The natural order of the records (the order in which the records were added
to the table) is maintained after the index is established.

Free download pdf