Indexing tables -> Speed ? 1

[waldemar]

General Access/DB Question...

What is the advantages of all the indexing possibilities?

Every table field has that "Index" property... When should I apply this? Will this slow down/speed up the database in which situations? (e.g. with fields that are being used as references in other tables) What other things should one keep in mind for better performance (query properties?)?

Regards
waldemar

 

[KenG]

This applies to Access97 / Jet 3.5. In general an index will speed up a query / search of a table. However over-indexing can slow it down. Just index fields that will be used in query joins, or where clauses. One rule of thumb is to not index fields with highly duplicated data e.g. boolean values, male/female.
Also, saved queries will, usually, run faster than queries that are generated dynamically i.e. using VB. Don't forget to compact your database, this also helps in overall DB speed by recompiling all queries next time they're run. It also recreates the index pages.
Avoid embedding expressions in queries. They can't be used during the complilation of the query.
If you have any questions about using an index - try it. It will always depend on your specific tables, indices, queries, hardware, network, etc...
I found most of this info on the MS web page. I don't have the URL, but it is from a White Paper about Jet optimization.

 

[waldemar]

Thanks KenG,

I'm program a database offline (incl. database/app. seperation) without server; and now I'm looking for ways to optimize and speed up the whole thing - no SQL Server but heavy data transmission (many data populated boxes to ease user navigation)...

waldemar