Re: improving performance by indexing query criteria fields

From: Tom Wickerath (AOS168RemoveThisSpamBlock_at_comcast.net)
Date: 02/06/05 

Date: Sun, 6 Feb 2005 14:36:45 -0800

Hi Paul,

> It also says to index fields on both sides of a join.

Yes, I've seen this too, however, you do not need to index any foreign key fields if you
establish relationships first in the relationships window. The reason is that Access
automatically indexes the foreign key field when you create a relationship.

Indexing fields will slow down an add operation, since any indexes must be updated at the same
time. I'd be really surprised to learn that indexes slow down delete operations. It seems to me
that an index would allow the appropriate records to be located faster in a delete operation.

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

I guess you can think of it as a "pay me now" or "pay me later" type proposition. You can pay a
slight time penalty at the time of adding a record, or pay it every time that a field is used as
a critieria. Note: you should also index fields that are used to specify sort orders.

> If I only have a few such fields, I suppose I could answer this
> question with some experimentation.

You might want to take a look at this article:
Use Microsoft Jet's ShowPlan to write more efficient queries
http://builder.com.com/5100-6388-5064388.html

My advice is to index fields that you use to specify criteria and sort orders. This is especially
important in a multi-user database, where the back-end is on a file server. Without proper
indexing, all records will be transferred over the wire before criteria are applied to filter
them out. With proper indexing, the index will be transferred over the wire plus just the
appropriate records (assuming the query optimizer can use the index).

Here are some additional articles you might be interested in looking at:

Information about query performance in an Access database
http://support.microsoft.com/?id=209126

How To Use QueryPerformanceCounter to Time Code
http://support.microsoft.com/?id=172338

How to optimize Microsoft Access when using ODBC data sources
http://support.microsoft.com/?id=286222

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

Optimizing for Client/Server Performance
http://support.microsoft.com/?id=208858

How to optimize Office Access and Jet database engine network performance with Windows 2000-based
and Windows XP-based clients
http://support.microsoft.com/?id=889588

Tom
________________________________

"Paul James" <begone.spam@forever.net> wrote in message
news:110d0kqefnlv9fe@corp.supernews.com...

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: improving performance by indexing query criteria fields
    ... establish relationships first in the relationships window. ... My advice is to index fields that you use to specify criteria and sort orders. ... important in a multi-user database, where the back-end is on a file server. ... appropriate records (assuming the query optimizer can use the index). ...
    (microsoft.public.access.queries)
  • Re: Showing certain record only
    ... In my database I have a toggle button that once pressed ... My Main form is called frmProject which gets it data from a query ... unbound textbox, txtZeros. ... I add the following criteria to the filtering query for the listbox, ...
    (microsoft.public.access.forms)
  • Re: Database Results Using Page Variable
    ... You can modify the Pick list example to use a hyperlink to pass the query (your criteria) ... Right above the 1st grey code just enter the ASP code to get the query value from the sending page ... But the database results in the include page should show ...
    (microsoft.public.frontpage.programming)
  • Re: Exporting numbers to a text file with more than 2 decimal digi
    ... the correct name and not modify the grid in the bottom half of the window. ... The Database is called GNSW_P756DEM. ... Double clicked on "Create Query in Design View" ... In the Field cell for the 2nd column, ...
    (microsoft.public.access.externaldata)
  • Re: Syncronize or merge
    ... > In A?????.mdb open the Database | Query window and click the New ... > window titled Show Table in the center. ... > Choose the Another Database option. ... >>> details of your data design. ...
    (microsoft.public.access.gettingstarted)
Loading