improving performance by indexing query criteria fields

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Paul James (begone.spam_at_forever.net)
Date: 02/06/05 

Date: Sun, 6 Feb 2005 12:48:18 -0800

In Access Help under Performance, it says that you can improve query
performance indexing any field used to set criteria for the query. It also
says to index fields on both sides of a join.

However, elsewhere I have also read that indexed fields can also slow down
certain operations like adding or deleting records, because Access has to
update the index when records containing those fields are added or deleted.

So evidently there is some performance benefit to be derived from indexing
criteria fields, but you also have to suffer a disadvantage.

If I only have a few such fields, I suppose I could answer this question
with some experimentation. However, I have about 10 databases in production
with about 900 query criteria and join fields. (I printed out a list of
them in the Immediate Window with some VB code).

Can anyone give me some guidelines for deciding when to index query criteria
and join fields, and when it's best to leave them unindexed, if you want to
improve the overall performance of your database?

Thanks in advance,

Paul

Relevant Pages

  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • RE: Filtering a subform using many combo boxes
    ... I followed you instructions to the letter and every one of the criteria ... I described the typical properties of all my combo boxes in my original ... open the query "qryProjects" in design view. ... I have a main table named tblProjectData that contains all the data for each ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)