composite indexes in Access

Dear experts -
I am now fine-tuning an application that is basically working well.
We have indexes defined in all tables on key fields.

However, we have not defined ANY composite indexes (if I'm using the term correctly - I mean an index based on more than one field).

The real workhorse of the system is a Project table, that has a number of child tables, most notably one called Project_Parts. Do we, for example, want to have a project_ID + Part_ID composite index on the parts table? Would this be standard, or not?

I used to work a lot in Foxpro, and indexes like this made a HUGE difference.

a related question - nowhere in our system do i 'tell' the code to use a given index; a programmer told me once that Access takes care of all this and 'knows' which indexes to use.

Can someone give me a bit of orientation on the above?
Thanks!
terpsichoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

terpsichoreAuthor Commented:
A further refinement of the question - I see, reading a bit online, that some people use a combination of fields as the primary key. Right now, I have the primary key in each table as simply the unique record ID.
Any guidance here, or articles that give a good overview of optimizing indexing in Access? Thanks -
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Right now, I have the primary key in each table as simply the unique record ID."
Autonumber I assume. Stay with that.

"Do we, for example, want to have a project_ID + Part_ID composite index on the parts table? "
Indexes are most useful for criteria, sorting - and grouping (in a query or report).
If ... you have for example a query that has filtering criteria (or sorting) on the fields Project_ID and Part_ID - in that order left to right, then a composite index *may* help. The *only* way you are going to know for sure is ... testing, specifically on a representative size recordset of data.

Tweaking indexes is more of an art form than not.

More (my standard post on indexes):

Here are some sources with a lot of good information.  I doubt if there is any once 'best' source, as there is much conflicting information on the subject.  Doing one's own R&D on the subject (read: testing) is probably the most prudent approach.

How To Optimize Queries in Visual Basic
http://support.microsoft.com/kb/172199

Microsoft Access Performance Tips to Speed up Your Access Databases
http://www.fmsinc.com/tpapers/faster/index.html

Microsoft Jet Database Engine Programmer's Guide - Chapter 4 - Queries
http://technet.microsoft.com/en-us/library/cc966377.aspx

Jet Engine Tuning Tips
http://msdn.microsoft.com/en-us/library/dd942824(v=office.12).aspx#odc_ac2007_ta_PerformanceTipsToSpeedUpYourAccessDB_JetEngineTuningTips



Note that indexes can be a double edge sword. Whereas they will (usually) most definitely speed up searches and filtering - almost like magic sometimes,  each time you add or delete a record, all indexes in the table have to be updated by Jet. So, if you have several hundred thousand records, then the updating time could be significant, depending on various aspects of your system and/or network connection is there is one, etc.

Indexing can be somewhat of an art form.  The bottom line is be judicious in your use of indexes. Don't just randomly index fields for no reason. In addition, test the effectiveness of your indexes on test (or live) datasets that represent the amount of data you expect to encounter ... and see for yourself how they affect performance, good or bad
.

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"that some people use a combination of fields as the primary key"
I have yet to see a valid reason for creating a compound primary key - as opposed to using the AutoNumber.

 Just for starters, a compound primary key immediately complicates creating relationships between tables, since now - say for the foreign key in that Many table of a One to Many relationship, you need all the fields (2.3.4, etc) of the primary key - instead of just one field - Long Integer - to match the Auto Number.

And another not so well known tid bit. When you enable Referential Integrity - again say between two tables in a One To Many relationship, Access automatically creates a 'hidden' Index on the foreign key - which you can see if you iterate the Indexes collection.

mx
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

terpsichoreAuthor Commented:
just what i was looking for - will start delving in to this. many thanks to all.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You are welcome.
Remember:  testing testing testing ...!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Couple of comments:

<<I used to work a lot in Foxpro, and indexes like this made a HUGE difference. >>

 There are two issues here; design and performance.

  Design has been touched on above.   In regards to peformance, JET will use a composite index, but only of the order of the index exactly follows the search given, so in general, you are better off with individual indexes and let JET do index merge-joins with Rushmore.

  JET doesn't have all of the Rushmore optimizations that FP had, but it has some of them.

  You also don't want to index anything with low cardinaility.  That is fields with few possible values (like a yes/no field, which has only two possible values).   JET indexes are ISAM based and ISAM doesn't do well with those.  It's faster to let it do a table scan.


  Also in regards to the design issues, you might want to read through:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2041-The-great-PK-debate-Natural-Keys-vs-Surrogates-again.html

  Which goes into key usage/design and even why you still might need a composite index (to form a constraint) even though your using a AN for a "PK".

<<a related question - nowhere in our system do i 'tell' the code to use a given index; a programmer told me once that Access takes care of all this and 'knows' which indexes to use.>>

  That is correct.  There is no HINTS or anything similar that can be used to tell JET which indexes to use.  It decides on it's own the best cost with the given indexes.

Jim.
0
terpsichoreAuthor Commented:
wow - thanks so much. truly insightful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.