Indexing?

[Guest]

Hi

When creating tables in Access 2007, do you have to create indexes to
improve performance or are they already built in?

Thanks for your help,
Louisa Holt

 

[John W. Vinson]

Hi

When creating tables in Access 2007, do you have to create indexes to
improve performance or are they already built in?

Well... some indexes are built in, but often not the right ones. If you define
a Primary Key it creates a unique index on that field or combination of
fields; if you define a relationship with integrity enforced, it indexes the
foreign key field. I don't know if the vexing annoyance of automatic indexing
of fields with names ending in ID or NUM is still in effect but it probably
is; these indexes are sometimes useful but often either duplicate existing
indexes (foreign keys frex) or aren't needed at all.

I'll try to think through what fields should be indexed aside from PK and FK:
any fields that are *routinely* used for searching or sorting, in particular;
any combinations of fields which should be enforced to be unique, etc.

John W. Vinson [MVP]

 

[Guest]

A very good question! I have A07 at home but really haven't looked at
indexing yet. I'm going to assume (yea I know) that it's similar to A03.

Every table should have a primary key. Hopefully it is only one field.
Access automatically creates an index for primary key fields.

If your queries join two or more tables, go to the Relationships window and
see if a relationship is defined between the tables involved. Hopefully they
are and Referential Integrity is enables as this will also create an index
with the Foreign Key in the other table. If not, see if you can link the
tables and turn on RI. There's a very good chance that it can not. In that
case mark sure that the linked fields in both tables have indexes.

Also index any field that is part of the Where Clause or Order By.

As mentioned before Access will automatically create an index for Primary
Key fields. It will also create indexes for field with any of the following
in any part of the field names: ID; key; code; num. In fact Access has no
problem creating two or more indexes on the same field. For example if your
Primary Key is named PK_ID, you will have two indexes for the same field
which is wasteful. Actually I think that this behavior might have been fixed
in A07.

Warning: Indexes aren't free. They also take up space and can slow down
inserts and updates. Don't go overboard on the indexing. If a table has fewer
than 1,000 records, chances are that an index won't do any good as Access
will just do a full table scan instead. If you really want to get into
indexing, google "access showplan". Once set up it shows how Access queries
are executed and what indexes are used.

http://builder.com.com/5100-6388-5064388.html

 

[Guest]

Hi John,

I just checked and good old ID;key;code;num are still automatically indexed
in A07.

 

[dbahooker]

Access is for babies

just use SQL Server
Access Data Projects allow you to use Enterprise Level tools-- tools
that can and will pick the best indexes for you.

I reccomend Access 2007 w/SQL 2005 Developer
or Access 2003 with SQL 2000 Developer

hope that helps

-Aaron


\On Mar 27, 6:13 pm, Jerry Whittle