The Ultimate Primary Key?

[DanG]

Has anyone found the perfect Primary key for the HX files in access yet?...Mike, Donnie, Rick, Henry…etc…?:confused:

I know we can improve upon TRK, DATE, RACE, and PGM

Obviously Access by nature would prefer a single Primary key instead of our current ‘compound version but how to do it?

Convert the text portions into numeric data and string together?

Hopefully someone smarter than I (which is a very long list around here) has solved this one.

Thanks.

[Rick]

I don't think it is worth the effort but you could replace the PGM with PP. That would replace a text field with a numerical field.

You could also add a table for assigning a numerical for each track, ie

1 AQU
2 BEL
3 FG
4 SAR
5 SA
etc

To me it more trouble than its worth.

Any other suggestions on this?

[DanG]

Thanks Rick,

That’s the exact workaround I was thinking about.

I’m just looking for increased speed and I’ve read that single numeric keys process at a different rate than compound ones.

Your right, if there’s no appreciable difference in processing speed I shouldn’t bother.

[Rick]

You will probably have better luck testing Indexing with a few fields, especially the one you use a lot in queries.

The fields in the Primary Key do not need to be indexed.

In the past I have had query run time drop from over 45 seconds to less than 5 seconds.

Just try one at a time and check your run time.

It normally reachs a time where it doesn't do much good to add more indexes.

[MikeDee]

maybe hurrikane will raed this and respond. He runs big Db and has done alot to speed them up.

I agree with Rick, I would want to know that a single joined field is alot faster the multiple joins.

The other thing is to take the fields you want from the 2 tables you are joining and make a new table with your selected fields from each. Queries on a single table (with indexes) will be faster then two joined tables.

[Rick]

The Access Cookbook mentions Rushmore.

A search of Access for Rushmore brings up "Improve performance of an Access database".

You might get some ideas by looking into that.

Rushmore appears to be something MS did to imporve FoxBase and is now used in Jet and MySql. Appears to relate to indexing.

[Rick]

This is from Absolute Beginner's Guide to Office Access 2003.

I couldn't copy Figure.

Working with Indexes
There's one important property we skipped over in the chapter so far: the Indexed property. That's because we wanted to discuss indexes as a separate subject. Deciding which fields to index in a table is an important part of designing a database.

An index in a database works much the same way as an index in a book. Suppose you want to find a particular topic in a book. You can leaf through every page in the book, looking for mentions of that particular topic. Or, more sensibly, you can turn to the index and use it to find the exact page where the topic is covered.

In an Access database, the indexes are not for your benefit, but for the benefit of Access. Although it might not seem like it at first, Access frequently has to look things up in the data you've entered. Suppose you create a query to display all the medicinal plants. Access can find that information by starting at the top of the Plants table and reading down, looking for plants that are medicinal. But if you add an index to the Type field in the table, Access can use that index to find the appropriate rows without searching for them.

Here's how you can add some indexes to the Plants table to make your database more efficient:

Open the Plants table in table design view.

Click in the LatinName field.

Set the indexed property for the field to Yes (Duplicates OK). This tells Access to create an index on the field but to allow you to enter the same value in this field in more than one record of the table.

Click in the CatalogName field.

Set the indexed property for the field to Yes (Duplicates OK).

To view all the indexes in the table, select View, Indexes or click the Indexes button on the toolbar. This opens a separate Indexes window, as shown in Figure 11.15. You can see that indexes have their own set of properties; when you're building your first databases, you should leave these properties set to their default values.

Figure 11.15. Viewing the indexes for a table.


Close the indexes window and save the table.

If you examine Figure 11.15 closely, you'll see that this table has some indexes you didn't add—Access sometimes creates indexes on its own when it knows they will make things work better. For example, the primary key field is automatically indexed, and numeric linking fields are automatically indexed as well.

You normally shouldn't add indexes to every field of your table because indexes make adding or updating data in the table slower. This is due to Access having to spend time updating the indexes. But remember, you usually enter data only once and then work with it many times. Here are some rules of thumb for deciding which fields need indexes:

Add an index to any field that is used to join two tables in a relationship (Access adds most of these indexes for you).

Add an index to any field that contains data you frequently search.

Add an index to any field by which you frequently sort.

Add an index set to Yes (No Duplicates) to any field where you want each record in a table to contain its own unique value. Access automatically adds such an index to the primary key field of your table.

The Absolute Minimum
Access makes working with most objects easy through its wizards and other tools, but sometimes you just have to get under the hood and make things better. In this chapter, you learned about some of the things you can do in table design mode, including the following:

How to control the data that goes into a field by carefully choosing the data type for the field

How the caption and description properties of a field let you supply helpful information for users of your database

How the input mask and format properties of a field let you control what data is entered and displayed

How the validation rule and validation text properties of a field help you limit the data to acceptable values and how to show a friendly error message in case of any problems

How indexes let Access find and sort data in your database more quickly

[DanG]

maybe hurrikane will raed this and respond. He runs big Db and has done alot to speed them up.

The other thing is to take the fields you want from the 2 tables you are joining and make a new table with your selected fields from each. Queries on a single table (with indexes) will be faster then two joined tables.

Thanks very much gentleman…

That gives me a good start.

Rick: Good refresher course on indexing. Something I obviously underestimate.

Open Question: Do you think it’s worth the effort to spilt HX4 up?

For example; Instead of one flat file to group all factors that are specific to the race and are redundant to each specific runner?...I.e.


DIST
SURF
DS
CLA
SEX
AGE
STB
PURS
CLM
EPR
FLD
VI
POST
QFIVES
RULE50
RUNKS
CHAOS (A personal favorite!):D

If nothing more than to reduce the file size.

I may be just re-arranging tables in a futile exercise. Certainly not my first, but I have so many custom fields that I really need to streamline the process. My first few months I used to just tack them on to HX4 or whatever tables that you had set up. After 18 months those tables need a chain saw and some serious re-organization.

Thanks for your help Rick & Mike.

[MikeDee]

Just my opnion...no facts to back it up, but I wouldn't do that. I have heard it said that access is not a real good Db platform to use when you have a lot of tables and are joining them to get to what you want.

I would go the other way and load up a single table with all the fields I could to keep it flat. I think this will be faster.

Others may know more may and jump in....once they get back from Vegas.

Another way to go if there are fields you don't use or need in your Db you can go into the file spec and simply skip the fields you don't want in your DB.