Solved

Indexing considerations for "large" Access files

Posted on 2012-04-03
11
320 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
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now