How bad are lookup-fields really, for performance?

[Premy]

Hello there,

I've lately come across some posts that condemn using lookup fields in tables. But how bad is this really? I mean it's quite a nifty feature, and it'd be really too bad if it hogs up resources, thus forcing us to leave it alone for performance considerations.

I'd appreciate if any of you guys/gals with more experience on the topic would like to share some, so I can take a better stance for future developement.


Regards,
Jaime

 

[boblarson]

I can think of two good reasons not to use them. I'm sure there are more.

1. You should not be letting users access the tables directly. If you do, you lose control and can't use VBA or macros to do things that you might want to do.
2. If you use lookup fields, then you will run into troubles should you decide to use forms as they are incompatible (not really, but for example if you have a form with a combo box which would store the id field but display the value, like the lookup in the table, you won't get it to display the name, it will display the id.

 

[RuralGuy]

Bob has really hit the nail on the head as far as I'm concerned.

 

[RoyVidar]

I don't know whether they have any performance implications, but I suspect they have, as what they do, is a join between the base table and the lookuptable to retrieve the description. So, say, with three table level lookups, I suspect that any operation on the base table, would involve join operations against all three lookuptables.

I'm probably not even going to test, as the other reasons for not using table level lookups, are reasons enough for me to avoid them

In addition to what is already mentioned, this http://www.mvps.org/access/lookupfields.htm sums it up rather good

(don't forget to read their 10 commandments, too )

 

[stopher]

I learnt Bob's point 2 the hard way.

What "nifty feature" is it you think you are losing Jaime?

My worry about the posts on Lookups in tables is that people are perceiving that lookups are not allowed at all. This is not what is being stated.

There is nothing wrong with adding a combo or listbox to a form that uses lookups to get its list. These control types are idea for managing what gets displayed versus what gets stored. You get all the nifty functionality that you would with a table lookup with the difference that it works. Moreover, they don't conflict with the points mentioned by Bob and they do not interfere with the database design.

The only thing you lose by not having table lookups is that when you create a form, a combo is created as the default control for a field. This "inheritance" is one of the failures of table lookups anyway (see 10 commandments mentioned by Roy), so just add combos manually to forms as and when needed.

Stopher

 

[doco]

What's the alternative? The more times the user is allowed to enter the same data the probablility of error increases to p=1.

 

[boblarson]

What's the alternative?

DON'T LET YOUR USERS TOUCH TABLES DIRECTLY! Use forms that you've created for that purpose. That is the way...

 

[KeithG]

Add a combobox or listbox to your form so the user just selects a value instead of typing the value.

 

[Premy]

Well due the the feedback of u guys, for which I'm very thankfull, I think I will have to forget altogether about lookup fields in tables. For me the 2 most important reasons are, based on the feedback here:

1. Performance degrades,
2. If I'd decide to upsize to SQL server afterwards, it won't work anyway.

This last point in fact is crucial, so crucial that I think it's a gross omission from MS (and even book authors on access) to not put a caveat from the very beginning against lookup fields.

Too bad, for I liked the way it would already create a ready to use combo for my forms (that's what I called nifty, Stopher). In fact that's the only use I had for them, for I never allow direct access to the tables (thanks for reminding anyway, Bob). I'll just create my combos manually now.

So for anyone starting out on a new DB, I now have 2 caveats:

1. turn off the names autocorrection feature (learned this one from other posts here)
2. do not use lookup fields in tables

Thanks to you all

Regards,
Jaime

 

[boblarson]

2. If I'd decide to upsize to SQL server afterwards, it won't work anyway.

This last point in fact is crucial, so crucial that I think it's a gross omission from MS (and even book authors on access) to not put a caveat from the very beginning against lookup fields.

What can we expect from MS when they, even in their sample Northwind database use outdated code (DoMenuItem) and also don't have other things properly designed.

In fact, MS should not have even put in the lookup fields in tables if they couldn't get it to be compatible with forms.

<said as if Oliver Hardy said it>
MS - Another fine mess you've gotten me in ...

 

[RuralGuy]

Premy,
The ComboBox wizard works just fine on forms and makes creating them a snap.