How to make my database move faster than Road Runner

[Squidling]

Hi Guys - am reasonably access-experienced and have just been tasked with creating a very large database (upwards of 300k records with about 40 fields repeated on at least 4 tables)

Obviously I want to tune this up as much as possible. I've never normally bothered with primary keys or indexes but I was hoping someone could give some tips on speed in general

Specifically I'd like to know what sort of performance increase you get from indexing vs primary key and whether its worth indexing many fields.

Also any general speed-up tips very much appreciated (eg I know that removing the indexes prior to importing the data then reloading them is more efficient than just loading the table with the indexes already in place - using the DROP INDEX SQL command)

Any and all help appreciated.

Squidling

 

[KenHigg]

Don't index just to be indexing. With every frivolous index, you take a performance hit.

Normalize and keep your app. focused on the primary need of the business. Don't do a lot of bells and whistles. (Amazing that you never did a pk)

Base your forms on queries, not tables.

Try to stay away from one size fits all type forms. A form with a lot of subforms, domain functions, etc will slow you down.

???
ken