Probably the single most important issue it looks at is that of indexing.
Good indexing can improve performance very significantly. If you have a
query which joins two tables for instance then if the columns (fields) on
which the join is made are indexed performance will be much better than if
not. The same is true of columns used to restrict a query's results.
Tables will normally be joined by means of a foreign key column in one table
referencing a primary key column in another table, e.g. a CityID column in a
table Addresses would reference a CityID column in a table Cities, so these
should both be indexed, uniquely in the case of the primary key of Cities,
non-uniquely in the case of the foreign key in Addresses. Designating a
column as a table's primary key automatically creates a unique index, so
there is no need to index it separately, and I think I'm right in saying that
by default Access automatically creates a unique index on a column whose name
ends with ID. If these columns were not indexed, however, the performance
analyzer wizard would probably identify this and recommend indexing. To be
honest, however, rather than use the wizard I think time is better spent
learning about these fundamental points of database design. By all means use
the wizard to se what it recommends, but only as a basis for critical
consideration of whether the recommended courses of action are part of a well
designed database.
BTW another function of unique indexes is that they control the integrity of
the data preventing duplicates of the same value s being entered in the a
column in a table when it should contain only one instance of that value.
You might have a table of US states for instance with the primary key being
the abbreviated form, e.g. CA, but have another column with the full state
name, California in this case. As well as the primary key column being
indexed uniquely the column of full state names should also be indexed
uniquely as these are themselves distinct values. But the same would not be
true of a table of cities with a numeric CityID column and a text City column
as city names can be duplicated. This something the wizard would not be able
to handle and relies on your knowledge of the real world to apply the correct
indexes.
One thing to be aware of is that indexing does impose some performance
deficit when updating tables as its necessary to update the indexes as well
as the tables per se. So be careful not to create too may indexes (Access
limits you to 32 per table anyway, including those inherent in relationships
created between tables). Another thing to avoid is the dreaded 'lookup
wizard' data type as this creates extra indexes among its other undesirable
effects; for more details on this see:
http://www.mvps.org/access/lookupfields.htm
More advanced programming techniques will sometimes drop indexes and then
recreate them after updating data to enhance update performance, but at this
stage you don't really need to concern yourself with that, just be aware of
the technique.
In summary my view of the performance analyzer is like my view of all
wizardry; by all means use them to see what they do, but do not treat them as
a substitute for a real understanding of how a well designed relational
database is put together. The time to use the wizards is when you understand
what they do, so that they can cut out some of the tedious drudgery, but
without usurping control of the database design from you as the developer.
The best wizard is still the one inside your head.
Ken Sheridan
Stafford, England
