Fog Creek Software
Discussion Board




MS Access Indexes

Does anyone know about how table Indexes (Indices?) are handled in Access? I am trying to speed up a query, and when I check out the indexes of a table, the "Primary Key" index is the last of several fields that are indexed. I figured if I move the PK index to the "top of the list", then it would take precedence. So I do this, save the table design, close the window. Then when I open it again, that index is down at the bottom of the list again! I even tried re-creating the table one field at a time, to force Access to create the indexes in the proper order, which it does, but then if I go back to that table, it has re-arranged the order again! Does it even matter what order the indexes are in? I would figure it would be best to have the primary key be the "first index", but perhaps I am misunderstanding the nature of indexes?

Also, does anyone know the difference between having an index with the name of your primary key field, and then the index that is actually called "Primary Key" that seems to be automatically created by Access?

Thanks!

  -Jordan Lev

Jordan Lev
Tuesday, September 23, 2003

1. The order of the list doesn't matter. 
2. There is no appreciable difference, IIRC,  between an index Access creates for your primary key and one you would create for the column(s) in your primary key. 

If you post some more info, maybe that would be helpful in trying to figure out how to speed up your query.

Jason Catlett
Tuesday, September 23, 2003

Thanks Jason. There's a lot of things involved -- not sure if I'd be able to post specifics. Basically, I was creating a recordset with a SELECT statement containing a SUM(). It was based on a query that was based on two other queries, each of which JOINs several tables.
I thought that re-jiggering the index might speed it up, but I guess it doesn't make a difference. What I just tried, though, that seems to speed things up (from 45 seconds to 1 second!) is INSERTING the records I need to SUM into a temp. table (local -- not linked to the network back-end), then running the SUM() query on the temp. table. Not sure why Access would take so long with one approach but take so little time with the other -- I somehow assumed that Access would be doing it this way "behind-the-scenes" , but just goes to show ya'...

  -Jordan

Jordan Lev
Tuesday, September 23, 2003

Jordan - Sorry if this is restating the obvious, but are you sure that a network bottleneck isn't to blame?

FWIW, one general rule of thumb that I use when optimizing queries in Access is to try whenever possible to select / elimintate records using joins rather than criteria (WHERE clauses).

I am certainly not an expert on the nuts and bolts behind the Jet database engine, but for whatever reason, I have noticed that table joins between indexed fields almost always seem to return records much faster than SELECT ... WHERE statements, especially when you have multiple WHERE conditions.

Tim Lara
Tuesday, September 23, 2003

==> ... Not sure why Access would take so long with one approach but take so little time with the other ...

Why? Because, for the most part, the Jet database engine does a good job -- but there are some particulary BrainDead(tm) branches in the query optimizer and when you structure a query such that it hits the BrainDead logic, you're screwed.

FYI: I'm not sure about newer versions, but older versions of Jet supported a (back-door) method whereby you could view the actual generated query plan. Any *real* database exposes this to the programmer/DBA in the front-end tools, but unfortunately Jet/Access doesn't. Anyway you used to be able to view the generated query plan(s) by setting a registry key:

Add the following key to the registry:

\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\3.0\Engines\Debug

Under this key, add a string data type called JETSHOWPLAN (make sure to use all capital letters). To turn on ShowPlan, set the value to ON.To turn it off, set the value to OFF.

When ShowPlan is turned on, Jet appends text to a file called SHOWPLAN.OUT every time a query is compiled. You must modify or compact the database in order to have a stored query show its query plan. It is also important to note that SHOWPLAN.OUT  appends new data for every new query plan. Leaving ShowPlan on could result in an
extremely large SHOWPLAN.OUT file.

Using the above SHOWPLAN.OUT file, you can actually see the query plan to determine if it's BrainDead(tm) or not. Unfortunately, there is no way to force a different plan (as most other DBMSs support) other than completely rewriting your query -- as you did with using the temp table inserts.

Can anyone verify if this still works with current versions of Access -- I don't have current version on this box to try it out. Thanks.

Sgt. Sausage
Tuesday, September 23, 2003

Yes, show plan does work (good suggesting by the way!)

You can also Check out:

HOWTO: Improve Performance of Applications Using Jet 4.0
http://support.microsoft.com/?id=240434

Also, check out this gem:
http://www.granite.ab.ca/access/performancefaq.htm

Also, sometimes re-writing part of a query will change things dramatically.

Also, since you are working a query on a query (that is View for you sql folks!), then often dumping the view, and placing the whole mess into one query can often help. You can wind up losing some indexing when you start stacking views on views (or so called quires on top of queries in ms-access). (views = quires in ms-access)

However, if you use a sub-select, make sure you restrict the records.

For example, I want a sales man who sold a blue car. In place of a standard join, we could use a sub-select.

select salresrepID, SalesRepName from tblSalesReps
where
salesrepID in
(select salesrepId from tblCarsSold where color = "blue")

Most sql engines will work the above quite nicely. Unfortunately, JET DOES NOT!!. You need to write:

select salresrepID, SalesRepName from tblSalesReps
where
salesrepID in
(select salesrepId from tblCarsSold where color = "blue"
and
tblCarsSold.salesrepID = tblSaresReps.salesrepId)

Note how I restrict the sub select to retrieve ONLY the records that can possibility have a match. (jet is not smart enough to realize that the only matches can be for a given id). While I am not suggesting to use a sub-select in the above example, often it can help for other types of queries, and eliminate views on views (just remember the above trick).

Of course in the above example, a simple classic sql join would be a much better solution.


Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. kallal
Wednesday, September 24, 2003

*  Recent Topics

*  Fog Creek Home