More Tables & Relationships or Fewer Subforms (Which is Faster)

[Bkper087]

I admit I didn't look too hard to see if this has already been discussed...



I find some of my databases running slowly. I am trying to design them better so they run faster and are more user friendly. I have started by changing the character limits and types on most tables. Additionally, I have been having a debate with myself about which would ultimately be faster. Creating more relationships or having fewer relationships and more sub-forms / reports. Obviously there are some relationships that make sense, like phone numbers. However, what about a city or zip code being separate if you may not know that information? It would make it far more complex to break things down in such detail, but it seems like it might run faster also.

Thoughts? Other ideas for making a database faster?

[CJ_London]

relationships are relationships - in a query it is the links between tables, in forms/reports it is subforms through child/master link properties - the db needs to be designed to optimise data input/retrieval so not a choice between relationships or subforms. Either way they define how the tables relate to each other, they do not have an impact on performance.

Ensure you have proper indexing - those fields reqularly used for criteria/sorting but not those with a limited range of values (e.g. day of week, yes/no).

Make proper use of parent/foreign keys - numbers will generally be faster than text - so don't use a telephone number or car registration as a key.

Ensure queries only bring through what is required, both in terms of 'width' (number of fields) and 'depth' (number of records).

Avoid domain and formatting functions in queries.

Avoid union queries and using like *something or like *something* in criteria.

Avoid using lookup, calculated and multiselect fields in table design.