Lookup function vs creating set

[Lawrence Fox]

If I just want to display some information from a related table, why should I create a set instead of using the relevant Lookupx() function in a calculated field?

Lawrence Fox
ComputerWizard Consulting

[JerryBrightbill]

RE: Lookup function vs creating set

The problem with lookup() functions is speed. If the table you are getting the value from is large, each lookup can be slow. Since it has to evaluated for each record as you fetch, this can be very annoying, especially if you have a couple lookups for each record. If the value is obtained from a linked table, the speed is nearly instantaneous. One the other hand, if the table is small enough, and there is only one lookup, a set may not be any advantage if the lookup speed is not an issue.

Jerry

[Tom Cone Jr]

RE: Lookup function vs creating set

Jerry,

It's funny. While I have no quantitative studies to back me up, I daresay you might have it backwards. The indexed lookups are almost instantaneous, and the traffic over the network is far less than when you push groups of lookup table records through the pipe as the user navigates through the primary table. My compass may be pointing south instead of north, but I favor simpler set structures, and retrieval of single record resultsets as compared to larger resultsets.

-- tom

[Steve Lippis]

RE: Lookup function vs creating set

What about referential integrity? I haven't used lookups so I'm not sure if this is an issue, but what if a value in the child table changes will the lookup in the parent table automatically change that field in the parent? If the value in the parent doesn't automatically change then I could see using a lookup for a point-in-time application.
Also, is it possible to have a "related" table without a set?

Steve

[CALocklin]

RE: Lookup function vs creating set

OK, how about a dissenting opinion that agrees with both sides? (Hmmm, maybe I should try politics!)

My guess is that Jerry is right when all you are doing is displaying the field BUT I usually opt for the simpler structure whenever reasonable as well. Not so much because of speed but because of all the other issues that start raising their head when sets start getting used for data entry. I find it very rare that a set is ever used only for viewing data.

On the other hand, I wouldn't suggest going to extremes. The more lookups you use, the longer it will take and the more problems may occur. So, I usually limit calculated lookup fields to only a very few. Actually, I've seldom needed more than one but others needs may be different.

Note: I recommend using calculated fields only on forms unless absolutely necessary to put them in the table. My philosophy is "Don't waste space with calc fields in tables unless there is no other way to do it." And, when you are just displaying info in another table, there is definitely another way to do it.

One other note:
Although it hasn't been mentioned, I would like to point out for anyone reading this that the plain Lookup() function can be very time consuming because it has to run a query rather than using a pre-existing index. Instead, use one of the LookupC(), LookupN(), etc. methods.

[JerryBrightbill]

RE: Lookup function vs creating set

Good questions. If a lookup is a calculated field on a form, it will reevaluate when the form is fetched. If it is a calculated field in a table, it only recalculates when the record is edited. Two very different situations.

I agree that a lookup() should be faster that linking a table in a set, but for some reason, calculated fields on forms using lookup functions always seem to slow down forms refreshing. I don't know the logic of the code behind a lookup or how it effects network traffic, but I have tried some forms across a network that used calculated fields with lookups, and also with a set structure, and the set structure was faster by a noticeable amount. However, in the case I mention, there was more that one lookup, so this may have cancelled out any speed differences.

If this is a concern, it is easy enough to try both approaches and see which is faster in any given situation.

Jerry

[CALocklin]

RE: Lookup function vs creating set

Steve,

I agree with your thoughts but I think this is one of those places where we (all of us) need to be very clear about our terminology. I believe your issue is best handled by filling the field with the built-in field rule lookup. Or, better yet, a default value (which may use a LookupX() function) so it won't get accidentally over-written by subsequent record changes.

I believe the issue here (as I interpret it) is just a matter of displaying certain data from another table on a form. What is the best method - create a set or use a calculated field which uses a LookupX() function?

This is one of those terms used in A5 that can cause confusion because they have multiple meanings - such as Lookup, Dialog, Script (Action/Xbasic/In-line), etc.

[csda1]

RE: Lookup function vs creating set

Tom,

I have actually tested this, and unless Alpha started caching table opens in recent versions, the lookupx() are slow because they do a table open and close between each invokation (which is a relatively slow operation compared to a linked table) and adds a whole bunch more network traffic (because of the open/close).

A linked set, doesn't keep reopening and closing the tables, index file and so forth, but just moves the pointers and fetches the new data. The data fetch is very fast (since it is all contiguous (sp)), and unless you can limit the lookupx(), is almost always the faster way.

Also, if you place lookupx() in a calculated field of a browse or form layout, it gets executed more than once (the way a5 does things internally), and thus actually is the equivalent of several lookupx()s.

If you place the lookupx() in xbasic code the overhead is negligible, but repeat it 10000 times, and it will slow things down alot. Try a query using a lookupx() to a table versus a reference to the child field in a set and you'll see the big difference.

But what you said is true, that one should strive for the simplest sets, as more links, modifiable children (changable, refererential integrity, add child record) add more network traffic and slow things down.

I always design the set to match the need, adding as many sets as needed to fit the various needs, just as you do, I'm sure.

Regards,

Ira

[csda1]

RE: Lookup function vs creating set

Hi Jerry,

Jerry Brightbill wrote:
-------------------------------
Good questions. If a lookup is a calculated field on a form, it will reevaluate when the form is fetched. If it is a calculated field in a table, it only recalculates when the record is edited. Two very different situations.


While in change or enter modes, a calculated field (defined in field rules) in a table is recalculated anytime any field of the record that is utilized in that calculated field expression is changed and the focus leaves the field. This can actually create a whole lot more lookups. The field is reevaluated at save time as well (in case an onsave types of events modifies the values again.

Not as bad as a form/browse calculated field (see my other answer to the reason this is really slow), but still more than once.

Regards,

Ira

[Tom Cone Jr]

RE: Lookup function vs creating set

Ira & Jerry,

Ok. It's time to take my compass to the shop for a tune-up, cause it's clear that in some cases linking in the related set has been found to be more efficient than the indexed lookups I've been using!

Thanks. -- tom

[Jim Chapman]

RE: Lookup function vs creating set

Hello Steve,

>>Also, is it possible to have a "related" table without a set?