Index Fields in a Table?

[HorseProfiler]

Regarding MS Access 2010,

I've read about the Pros and Cons of indexing fields and would appreciate any thoughts on whether to index or not. An index will greatly increase the speed of retrieving records, but takes time to update the index as you add and delete records.

I'm upgrading to Windows 7, should I use the 32bit or 64bit OS? Also, should I turn OFF automatic updating and choose the manual option?

Liam
Horse Profiler

[Rick]

When I am doing research with something like the ROI Report I would add indexes on a few things in the query.

I haven't done that in a while.

As I recall adding indexes reaches a point where it doesn't do much or any good to add more indexes.

My suggestion would be to add them one at a time and test/time them.

I left the updates on automatic. I figured what good were the indexes if they were not up to date.

The items in the Primary Key are already indexed.

I would go with the x64 version. For one thing is is supposed to be able to use more memory.

I have 6 GB of memory and a while back I was running out of memory. I think part of the problem was have an Intel 80GB SSD that was running out of space. I ended up moving Virtual Memory to another drive and giving it plenty of room. I haven't had it lock up on me since with no memory since.

[DanG]

Rick if light years ahead of me on computer knowledge (along with 45 other people on the board :)) so I would certainly listen to him. My experiences with indexes (other then the usual primary fields) is on average they did more harm then good. For each query etc that they improved, they degraded 5 others. I spent a large amount of time setting up different sub indexes and wound up removing all of them.

BTW: If anyone is in the practice of ‘creating’ tables from imports keep an eye out for indexes that Access can assign on its own. Sometimes a field full of trainer names for example will get a default index that can really slow things down when you’re moving massive data around.

[Donnie]

The rule of thumb to use for indexing is:

1. Index fields that you sort on regularly in queries/reports.
2. Index fields that you group by regularly in queries/reports.
3. Index fields that you regularly set criteria against in queries (be careful that you are not over-indexing ALL fields you set criteria against. TRK, DIST, SURF would be good fields to have indexes set on.

[HorseProfiler]

Your feedback is greatly appreciated. From my reading, indexing fields (other than primary key fields) would increase the time to update the index "IF" you add and delete records often. I also noted under the "Table Tools" tab ... "Indexes", Access would occasionally assign an index to random fields during the import process.


Liam
Horse Profiler

[DanG]

After reading Donnie’s post Liam; it occurred to me the reason I had an issue with indexes. As my DB grew beyond Access limits I went to multi-table relationships splitting the HX tables into sub groups. This became a real issue with indexes (other then the primary keys). If you’re in a flat file, I think they will improve performance as Donnie & Rick pointed out.

[Donnie]

Dan is correct; the more tables you break the data down into and the more relationships you build amongst those tables and then use in your queries, the slower things will naturally become.

Liam- be sure you are compact and repairing on a regular basis. If you "churn" data (in and out every day) then the compact and repair does 2 things = returns the space freed up by the deleting back to the OS/hard drive (that's the compact part) and removes and resets your indexes (that's the repair part).

Databases continuously only get larger in file size until you compact. In Access, you will eventually hit the 2GB limit.

The more the "movement" (add/delete, add/delete) of data, the more fragmented your indexes become. It's like constantly adding and moving and changing the contents of a textbook, but never updating the index in the back of the book. Terms are listed to be on certain pages, but when you go to look for them, your additions and deletions have moved the terms to other pages. What good is the index of that textbook then?

[HorseProfiler]

Thanks to each of you for the feedback! I realize a DB is always a "Work in Progress", and I'm looking forward to starting my membership with HTR.

With my membership can I download past data? Currently I'm building my DB with the 3 days included with the HTR2 download?

Liam
Horse Profiler

[Rick]

When you sign up you will find about 45 days of past race files.

Right now they go back to 26 Apr.