Field Lookup Efficiency

nschroeder

nschroeder
Local time
Today, 01:10
Joined
Jan 8, 2007
Messages
186
Could someone provide some general guidelines as to determining the most efficient options for field lookups (value list vs table)? And in the case of a table lookup of a text value, at what point would it be more efficient to switch to having your field and lookup value a two-column lookup with the 1st column being an auto-number and the 2nd column the text value, vs just a single-column text value.

The determining factors I'm wondering about would be the text field length, and the number of expected records in the database. For example, if the text field is 10 to 20 characters in your main table as well as the lookup value, and your table is expected to have only a few thousand records, is there any noticable efficiency advantage to making your lookup table two-columned with an auto-number key? Most non-programmers would just use a text value. Programmers want to do things the right way, but at what point is it worth the extra effort?

Thanks for your help.
 
Without looking at the efficiency in terms of operating speed, etc., - IMO it is easier to administrate value lists. Just write a simple form interface and give permission rights for the form level access.

In this manner, noone is bothering you to update them at the table level - you have effectively outsourced the upkeep and have freed up time better spent elsewhere.

-dK
 
OK, you asked for guidelines. Here's some random rumination on the subject.

Efficiency is an issue when you need to use the same name in multiple places or need to store that name a LOT or when the same name appears more than once in the same field in the same table (but obviously different records).

Say for example you need to store 20 characters for the name and you have a thousand different names. Suppose further that you have 10,000 records in one table with that name as one of the fields. To store 20 characters 10,000 times is 200K bytes. To store the PK of the lookup table you are using at most a LONG for the autonumber, which is 4 bytes. Which is 40K bytes to store. That's a 80% savings on that field, 160Kbytes you didn't have to store. And a JOIN makes it easy to recover that name when you really needed it, since autonumber PKs are a numeric index that is trivial to look up.

Now if you have to use those same names in a second table of the same size, you save another 160KBytes. Simple linear savings.

Another matter of practicality is that when you are searching for records and have to do a dreaded "relation scan" (i.e. there is no index so you do one record at a time), the buffer in which you do this scan is fixed in size. If you store an FK rather than the full 20 byte name, you saved 16 bytes per record, which maybe means that more records can fit into a single buffer. Which means you search more records per disk read, which is an efficiency sort of thing.

Here's the offsetting consideration. If your disk drive is big enough and you know for a fact that your file size needs will never ever in a gazillion years get bigger, you can forego setting up a relationship and just copy the data wholesale into the table. Modern computers (desktops and even laptops) are big enough and fast enough to more or less offset the small delays involved in a small database. (The odds are that by the time that the next "gazillion years" have passed, you'll be on a newer, larger system anyway... ;) )

"Doing it right" is always right but the question is whether doing it another way could also be right. Myself, I more or less automatically choose to split the tables and have a PK lookup or JOIN situation between lookup values and FK fields in my other tables. But that is to a large degree a matter of habit, not a matter of anything else. I guess I've schooled myself to think that way. On the purely aesthetic sense of "doing it right because it is right" then I would always do it right just to avoid falling back into bad habits. But that has nothing to do with practicality. It's just that over the years I've developed LOTS of bad habits.
 
Lets not forget Microsoft says Access is optimized for searching LONG data type fields.
So any time you have an indexed key, a data type of LONG is the most efficient.
At least according to Microsoft, in Access 2003 (do not know if it changed for 2007).
 
That's all good help. Thanks each of you. Another question: Is the space required for a text field base on the defined field length or the actual value in the field?
 

Users who are viewing this thread

Back
Top Bottom