improving performance by indexing query criteria fields

[Paul James]

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

 

[John Spencer (MVP)]

Personally, I index any field that I use frequently (whatever I define as
frequent) in searches or sorts or that seems to take a long time to my users.
If I've set up relationships in the relationship window I don't index the fields
involved in the relationship, because as far as I know Access automatically
builds these indexes.

If you don't use the relationship window to set up the relations, but just do it
in queries then you do need to set up the indexes.

There is a small hit when adding or changing records, but usually unless you are
adding a lot of records at one time through an Insert query or some loop in VBA
code, the extra time is not noticeable to the user. Let's say for argument's
sake, that adding a records with 5 indexes takes an extra second - the user
won't notice, but if you were doing that through an insert query that added 600
records, that would cause 10 minutes of added time. In real life, you are not
talking a second, but more like a few milliseconds.

In other words, add indexes when it makes sense. Also, indexing boolean fields
rarely helps unless the distribution of yes/no is heavily skewed towards one
choice or the other and you usually are trying to get the value that least often occurs.

All the above is MY OPINION and observations through a wide variety of databases.

 

[John Vinson]

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?

I'll agree with John. The cost you pay in slower updates and increased
database size is *usually* outweighed by the improved performance on
searching.

However, don't gild the lily! Access automatically creates a unique
Index for each Primary Key field, and a nonunique Index for each field
used as a foreign key in the Relationships window; if you're unwise
enough to use the Lookup wizard, it creates ANOTHER index for the
fields in both tables involved, even if indexes already exist; unless
you've turned it off, certain fieldnames (those ending in ID or NUM
for example, see Tools... Options... Tables) get automatically
indexed. Check each table's Indexes collection to see what indexes
you already have (or the Documentor), and delete any redundant ones.

Back when I was good friends with an Oracle DBA, I learned that she
spent a great deal of time and thought tuning tables by determining
which fields should be indexed and which should not. At times it can
be a bit of a black art.

John W. Vinson[MVP]

 

[Tom Wickerath]

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
________________________________


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

 

[Paul James]

Thanks for the information, gentlemen.

I'm convinced -I'm going to index my criteria and sort fields. In that
regard, I have two additional questions:

John Vinson had said:
you already have (or the Documentor), and delete any redundant ones.

1. I know how to count the number of indices in a table using

tdf.Indexes.Count

but that just gives me an integer telling how many indices there are in the
table. Is there another property or method I can use to reveal more
information about the Indices in a table?

(I usually establish joins in the relationships window wherever appropriate,
and I never use the Lookup wizard. However, some of my design predecessors
(who are long gone) may have done so, and I'd like to know how to check to
see if I've got redundant indices, in order to remove them. How can I
examine the indices for a table to tell whether I have this situation?)

2. How do you delete an index - is it simply by going into table design view
and setting the Indexed property of a field to "No"? And if there are two
indices for that field, will it remove both?

Thanks again in advance,

Paul

 

[John Vinson]

but that just gives me an integer telling how many indices there are in the
table. Is there another property or method I can use to reveal more
information about the Indices in a table?
...
2. How do you delete an index - is it simply by going into table design view
and setting the Indexed property of a field to "No"? And if there are two
indices for that field, will it remove both?

A couple of ways. In table design view you can use a GUI - click the
"indexes" icon (looks light lightning hitting a datasheet). This will
show all of the indexes by name in the left column, and their
corresponding fields (an index can have up to ten!) in the right. To
delete an index simply select it and hit the <delete> key.

In VBA you can loop through the Indexes collection:

Dim idx As Index
Dim tdf As Tabledef
Dim fld As Field

For Each tdf In CurrentDB.TableDefs
Debug.Print "Table: " & tdf.Name
For Each idx In tdf.Indexes
Debug.Print " Index: " & idx.Name
For Each fld In idx.Fields
Debug.Print " " & fld.Name
Next fld
Next idx
Next tdf

An index has a Delete method as well.

John W. Vinson[MVP]

 

[Tom Wickerath]

Hi James,

Is there another property or method I can use to reveal more
information about the Indices in a table?

I think the easiest method is to simply use the built-in database documenter. Click on Tools >
Analyze > Documenter. Select the tables tab. Select all tables. Click on the Option... button.
Select "Names and Fields" (or "Names, Fields and Properties") under the heading "Include for
Indexes". Don't check off any options under "Include for Table" and select Nothing under
"Include for Fields".

You should be able to go into table design view, and click on View > Indexes to bring up the
Indexes window. Delete any duplicate indexes here.

Tom
_____________________________________


Thanks for the information, gentlemen.

I'm convinced -I'm going to index my criteria and sort fields. In that
regard, I have two additional questions:

John Vinson had said:
you already have (or the Documentor), and delete any redundant ones.

1. I know how to count the number of indices in a table using

tdf.Indexes.Count

but that just gives me an integer telling how many indices there are in the
table. Is there another property or method I can use to reveal more
information about the Indices in a table?

(I usually establish joins in the relationships window wherever appropriate,
and I never use the Lookup wizard. However, some of my design predecessors
(who are long gone) may have done so, and I'd like to know how to check to
see if I've got redundant indices, in order to remove them. How can I
examine the indices for a table to tell whether I have this situation?)

2. How do you delete an index - is it simply by going into table design view
and setting the Indexed property of a field to "No"? And if there are two
indices for that field, will it remove both?

Thanks again in advance,

Paul

 

[Paul James]

That information is exactly what I needed.

Thanks for the information about using the Documenter to examine indices,
the lightning tool and the code to loop through the indexes collection.

Thanks also for your comments about the benefits of indexing fields used in
query criteria, sorts and joins.

Paul

 

[Marshall Barton]

Thanks for the information about using the Documenter to examine indices,
the lightning tool and the code to loop through the indexes collection.

Be sure you understand that the Indexes window (in table
design view) will **not** show you the foreign key indexes
that are automatically created when you set up referential
integrity in the Relationships window. As others pointed
out, these are the ones most likely to be duplicated.

AFAIK, the ony way to see all the indexes is to use a code
loop like John posted. (The Documenter seems to do this,
but I find the output too cluttered to be useful).

 

[Paul James]

Thanks for this additional information, Marsh.

AFAIK, the ony way to see all the indexes is to use a code
loop like John posted.

Does this also apply to redundant indexes automatically created by the
Lookup Wizard?

 

[John Vinson]

Does this also apply to redundant indexes automatically created by the
Lookup Wizard?

Alas, yes; they're carefully concealed. Sorry! That's burned me more
than once and I should have remembered it. Thanks for the catch,
Marshall!

John W. Vinson[MVP]

 

[Tony Toews]

Agreed on the rest of your posting.

Also, indexing boolean fields
rarely helps unless the distribution of yes/no is heavily skewed towards one
choice or the other and you usually are trying to get the value that least often occurs.

I vaguely recall a posting by someone in comp.databases.ms-access who
thought the same as you. He was quite surprised when he did some
timing tests and discovered indexing a boolean field shorted the query
time by 90%.

I do not recall if the data was heavily skewed.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Paul James]

I vaguely recall a posting by someone in comp.databases.ms-access who

thought the same as you. He was quite surprised when he did some
timing tests and discovered indexing a boolean field shorted the query
time by 90%.

Thanks for this additional information, Tony.

Paul

 

[Ken Snell [MVP]]

I routinely index my boolean fields.... don't know that it hurts in any way,
and figure that it can only help!

 

[Marshall Barton]

Agreed on the rest of your posting.


I vaguely recall a posting by someone in comp.databases.ms-access who
thought the same as you. He was quite surprised when he did some
timing tests and discovered indexing a boolean field shorted the query
time by 90%.

I do not recall if the data was heavily skewed.

I believe that was David Fenton and, if I remember
correctly, I was quite suprised that it didn't matter if the
data was skewed or not.

 

[Tony Toews]

I routinely index my boolean fields.... don't know that it hurts in any way,
and figure that it can only help!

Well, if I'm wrong then it could affect performance.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Ken Snell [MVP]]

Well, if I'm wrong then it could affect performance.

Huh? Wrong?

 

[Tony Toews]

Huh? Wrong?

Yup, it happens. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Paul James]

More fuel to the fire. I shall index those fields as well.

Thanks, Marsh.

 

[Paul James]

You guys can make mistakes?

Hey, I want my money back.