Don't Index Fields with Lots of Identical Data

Don't apply indexes to fields that contain much the same data. For example, indexing a Yes/No field is almost always a performance degrading operation. Similarly, if you have a number field that only contains two or three values, and index wouldn't be a good idea. To check the number of unique entries in an index, use the Access DistinctCount property. Compare this value to the number of records in the table and you can quickly see if the index is doing you any good.

This tip comes to you courtesy of Adam Cogan at SSW who can be found at http://www.ssw.com.au.  SSW runs the popular Access Users Group in Sydney.

Indexed Fields Should Generally Be For A Physical Purpose

Until such times as you are having performance problems, only index those fields where you are joining tables together in queries or where you have to avoid duplicates in a field or group of fields.

Indexes take up space in your database and whenever you save a record, you must also save the index.

Watch Out for Auto Index

If you open up Access and select Tools ... Options and then select the Tables / Queries tag you will find a field called 

Auto Index on Import/Create:

If you have any fields in your database with these prefixes or suffixes, you will find that you have indexes on the field whether you like it or not.  You may as well clear the "Auto Index on Import/Create" text box on this tab as this is a very unnecessary feature.

The standard keys featured here are

ID;Key;code;num

So look out for those fields cause your database will be bigger and slower than you think.

In Access 2007, you can check for and change this setting by viewing these pictures.

Watch Out For Replication Indexes

If you happen to come across a database that has replication indexes and the database no longer synchronizes with other databases, you will be likely to have fields with replication indexes that are not needed. These may have the name of GUID.

Before you take any of my advice on this issues, BACKUP your database. OK you have done that now you can check if the replication indexes are still in place. If they are, deleting the index will save you a lot of space in big tables. This is quite easy to do.

Now another thing to realize about these replication fields is that they are very large, about 30 characters in size.  Multiple this by 100,000 rows and you have a lot of space when compared to a long integer field.

The next step in clearing up replication index fields from a database is deleting the field itself. This sounds easy but it isn't as you need to transfer the table to another database using append query.  Anyway there is a good solution, use this tool from trigemal
http://trigeminal.com/lang/1033/utility.asp?ItemID=11#11

FYI: I worked on one database where we reduced the size of the database by 40% by removing the indexes and the GUID fields.

These tips was provided to you by Garry Robinson - Software Consultant
Try out our popular Access shareware Graf-FX  @  http://www.vb123.com/graf/

Published  1999-08