Indexing considerations for "large" Access files

Greetings, wizards all ...

I don't know what Access 2007 considers a "large" table, but I have an app that has a number of related tables, and on a recent visit I noticed those tables are nearing 16,000 records each, representing about 8 years of activity. The tables have about 20 fields each.

Are there special indexing considerations I should be applying to tables of this size? Or does that number of records not even come close to a level at which I should be concerned?

Each of these "large" archiving datasets has a primary index. The index field is populated with a Long Integer generated as an autonumber during the creation of the record in a dedicated input table. After the record is created in the input tables, it's moved to storage in an archive file, indexed on the ID assigned during the initial input.

This is in all likelihood a pretty silly question, but I just want to make sure I'm applying all prudent record handling procedures to guarantee the integrity of the data as it heads into the 20 to 25,000 record range.

Many thanks, my friends...

John
jofoco4Asked:
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.

peter57rCommented:
In terms of record numbers then this is not significant at all.

After you run a Compact Database how big is the file?
0
lee555J5Commented:
1. You should ALWAYS be mindful of performance, even in the beginning with very few records. Someday, you'll have 16-25k records, and it will matter.

2. As a general rule you should index any field (field combo) that might be searched or used in a relationship. Also, keep in mind the max number of table indexes in 2007. See here. You get 32/table in 2007.

3. You should index the fields mentioned in 2. even if they aren't unique.

4. You should only index the fields necessary. Excess and unnecessary indexing can actually hurt performance with lots of SQL INSERTs, UPDATEs, and DELETEs because all indexes must be updated with the new rows and data.

See here for some indexing basics.

Lee
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I would consider this a small database ... with 16K records ... although how many tables are we talking about?

2nd question: Have you done a Compact & Repair ?

Indexing is most relevant when you are concerned with sorting and/or filtering of records.  Indexing is a double edged sword ... great for speed increases with filtering/sorting, but slow when records are added, as all indexes have to be recomputed by JET.  But again, 16K records is relatively small.

Indexing is an art form more than not ... and as a db grows in number of records, it s/b revisited.  You really have to do testing, trail and error ...

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"2. As a general rule you should index any field (field combo) that might be searched or used in a relationship."
There is no need to index fields used in Relationships (eg Foreign key) because IF ... Referential Integrity has been enforced, Access creates a hidden index (p/o the 32 max btw) on the foreign key field. If you add that index yourself, then you have duplicated (and wasted) an index.

Again, indexing is an art form ... and while there are some general rules for sure, you *must* experiment and test on the dataset available to see what 'works' and what doesn't really change performance.

And the max number of indexes has always been 32 - as far back as I can remember.  Again, Access creates certain hidden indexes - as I indicated above.  You can confirm this by iterating through the Indexes Collection.

mx
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
John,

  One place you can start is the database performance analysis tool.  However don't follow it's recommendations blindly.  Try and understand why it's making the suggestions

  As has been said, proper indexing requires analysis, testing, and then repeat and there is no "right" answer really.  It comes down to what works for your app and the way it is used.

  A couple of gotcha's though to avoid:

1. Don't double index as MX indicated.

2. On compound indexes (a key made up of more then one field), you must search based on the same sequence of fields in the key or the index is ignored.  Often, you are better to index the individual fields and let the query parser perform and index merge/join

3. Don't index fields with low cardinaility, such as yes/no fields (only two possible values).  Very often a table scan will end up as being faster then the index.

  There is also an un-supported tuning tool called JET SHOWPLAN, which allows you to see how a query is being executed.  There is also the ISAMSTATS method so you can get various stats on how the database is being read.

  However with only 16K recs, as the others have said, that's not very big at all and I doubt you'd need to resort to either of those.

4. Any of the domain functions (ie. Dlookup()) should not be being used in a SQL statement or query.  The query parser cannot optimize these statements.  Instead, use joins or subqueries to get the data you need (all the Domain functions represent SQL statements anyway and in a query, there's no reason not to write the statement directly).

Jim.
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:
"2. On compound indexes (a key made up of more then one field), you must search based on the same sequence of fields in the key or the index is ignored. "

Just to keep the terminology straight ... I think you mean:

"a key made up of more then one field" >> a index made up of more then one field

"you must search based on the same sequence of fields in the key or the index is ignored"
>> you must filter/sort based on the same sequence of fields in the order as the appear in the index or the index is ignored.


mx
0
jofoco4Author Commented:
Many thanks for your further suggestions and external references.

The most important elements I take from this are :

1) Access eats 16K record tables for breakfast,
2) Indexing sounds as though it's more a 'use of resources' and performance issue than it is a 'mechanical relationship of data' issue, and
3) While there are a couple of do's and don'ts, there is no right answer to an indexing strategy.

I get and understand this, and it makes sense to me. The distinction that indexing is more about performance than it is about Access being able to remember that "all redheads (in the main table) are great guys (from the related table)" is of particular value to me. (!)

Thank you muchly!

John
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
@MX,

<"a key made up of more then one field" >> a index made up of more then one field>>

No, I believe that to be correct.  A key is made up of one or more fields and a index is a collection of keys.  I suppose it's a matter of semantics with Access whether you can consider a key to exist seperate from an index, but in my book you do.

@John,

<<) Access eats 16K record tables for breakfast,>>

   Yup.  50K-100K is a fair sized table.  100K - 500K I would consider as large and you need to start watching your indexing carefully.  Getting up over 500K is a very large table and indexing becomes critical.  2-3 Million would be about as far as I would push JET.

<<2) Indexing sounds as though it's more a 'use of resources' and performance issue than it is a 'mechanical relationship of data' issue, and>>

  Yes.

<<3) While there are a couple of do's and don'ts, there is no right answer to an indexing strategy.>>

  Right.  As MX said, it falls more into the "art" category then "here are the rules".  

Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"and a index is a collection of keys."

Well, not really. The only Key in an Access is the Primary Key (if it exists) ... unless you have a foreign key(s), which if the Primary Key in the related table is a compound key (multi-field), then the foreign key would have to contain all those fields.  And, if RI is enabled, then Access will create a Hidden Index on that foreign key. And that foreign key (in this example) is still a collection of Fields.

As you can see from the image, this multi-Field Index (aka compound index) is a collection of Fields, not keys - unless I suppose F1, F2 & F3 happen to all be Foreign Keys, which I see as somewhat rare.

So, I don't think it's a question of 'semantics'.

Multi Field Index comprised of 3 fields example
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<So, I don't think it's a question of 'semantics'.>>

  OK, so:

<<As you can see from the image, this multi-Field Index (aka compound index) is a collection of Fields, not keys - unless I suppose F1, F2 & F3 happen to all be Foreign Keys, which I see as somewhat rare.

  Without defining an index, can I not walk a table and determine the key value (compound or not) for each record?  The answer is yes.  To me, that says a key is not an index. I don't need an index to have a key.

  An index is nothing more then a collection (a list) of keys.  When you define an index in Access, you are describing the key as a collection of fields, but you are not defining it.  

  Working in other languages, often an index is defined simply by saying the key is X bytes long, so Y number of KIE's (Key Index Entires) fit on an index page of Z size,  and the index is not aware of the key structure.

   Access puts more window dressing on it;you describe the key for the index and it handles all the details of how long the key is, how many keys fit on a page, how many levels are in the index, etc., so the line seems blured a bit between a key and a index, but they are not one in the same thing.

 Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry, but I do not agree.

"I don't need an index to have a key."
Nowhere did I imply that.  

If you look at the image of the Indexes Dialog, where do you see the work 'key', other than if you want the Index to be the Primary Key?  Clearly you see the word Field ... and clearly the Index(s) is(are) made up of Fields.  

mx
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.