Solved

Indexing considerations for "large" Access files

Posted on 2012-04-03
11
340 Views
Last Modified: 2012-04-04
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
0
Comment
Question by:jofoco4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37803935
In terms of record numbers then this is not significant at all.

After you run a Compact Database how big is the file?
0
 
LVL 13

Assisted Solution

by:lee555J5
lee555J5 earned 133 total points
ID: 37803945
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
 
LVL 75
ID: 37803959
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 133 total points
ID: 37803978
"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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 134 total points
ID: 37804015
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
 
LVL 75
ID: 37804063
"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
 

Author Closing Comment

by:jofoco4
ID: 37804081
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
 
LVL 57
ID: 37804234
@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
 
LVL 75
ID: 37804327
"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
 
LVL 57
ID: 37805841
<<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
 
LVL 75
ID: 37807179
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question