Query Performance

[bginhb]

I'm working on improving the performance of an Access 2003 application. There are design issues with the database but I don't see any clear way get the Users to agree to a rewrite at this time.

I'm frankly surprised at how little information is available about "best practices" when designing indexes for key tables in Access applications ... hopefully someone can enlighten me.



I think I've read most of the info provided by Access on this subject but I'm left with the feeling that I don't really understand how it works so please bear with me.

1. A work table is an important aspect here. The table is rebuilt for every run so first it is emptied then filled using an Append query.
2. This table is then updated many times (based upon some data conditions external to the table) but only a few selected records take part in each update.

The selection is usually based on three columns/fields.

Questions:
Should each of the columns involved in the selection be indexed? I would think yes.
Would one or more multi-field indexes perform better? What would be the negatives of a multi-field index approach?

Thank you for any assistance,
bginhb

[orange]

see http://www.accessmvp.com/strive4peac...Chapter_04.pdf
I would delete the Work table at the end of each run, and
You should do compact and repair to regain space used by Access.

[bginhb]

Thanks for directing me to this excellent resource Orange. Unfortunately in some areas it appears that Access has changed so much that the material addresses capabilities that no longer exist or are hidden from view.

I refer specifically to the table defaults settings which no longer seem to be available with 2007. I suppose that I could save my accdb as an mdb then open it with 2003 but I don't know if my settings would remain in effect if I then converted back to accdb. Does anyone know?

bginhb