Query Efficiency

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

At my job it was required of me to make an application in
Access to create certain reports. This application really
should have been made in a different environment due to
the number of users and the amount of data being
processed. Anyway, it takes a while for queries to run
due to the amount od data they need to sort through and
also due the the speed of the server that the data is
housed on. Is there any way to speed up these queries or
how quickly they process information? Any help would be
greatly appreciated.

Chad
 
Chad, this is a very broad question.

A well-designed and optimized database should give almost instantaneous
performance with tables that have hundreds of thousands of records and up to
a couple of dozen users.

Some suggestions:

1. Ensure you have a suitable data structure. Read up on "normalization".

2. Ensure you have indexes on the right fields - particularly those used in
filtering or sorting.

3. Avoid techniques that prevent Access using the indexes, such as the LIKE
operator with a wild card at the start, or calling functions in the query
(esp. domain aggregate or user-defined functions, or using IsNull() when Is
Null would do).

4. Reduce concurrency issues by splitting the database and giving each user
their own copy of the front end.

5. Turn off the properties that destroy performance, such as Name
AutoCorrect (Tools | Options | General) and SubdatasheetName (Properties of
each table).
 
Back
Top