Lookup VS relationship

[Ertjie]

I was wondering, in terms of performance, which will be best: Say i have a calculation in a field from TableA that needs a value from a field in TableB (tables are related). Would it be best to have a lookup field in TableA that looksup the value from TableB then use it in the calc or would it be best to use the relationship between the two tables to directly reference the value in the calculation? I might be completely wrong (still a FM amateur) but my logic argues that a lookup does the lookup once and thats it in terms of processing, where as using the relationship has to each time process the relationship to get the value and thus creates more overhead etc. But what effect would having the extra fields (if using lookups)have on performance as this still needs to process? Hope this makes sense and isn't a completely stupid question!

[Vaughan]

I was wondering, in terms of performance, which will be best...

Define performance.

The looked-up fields will increase file size and disk usage which will impact backup times and may slow down imports as fields get indexed. They will also require scripted processes to update the fields if the original value changes and this can lead to slow-downs and problems with locked records not being updated correctly.

IMHO you're "prematurely optimising" something that may not be a problem.

I recently built a process for a client that took over 4 hours to run. I could probably spend a couple of days optimising it, but it'll only be run twice a year so there is almost no gain in efficiency for the effort. On the other hand, saving 1 second on a process that may be run dozens or hundreds of time a day could result in huge productivity gains.

[comment]

a lookup does the lookup once and thats it

That's correct. And if the related value changes, the looked up value remains unaffected. That is something you should consider before comparing performance.

[Ertjie]

Thanks guys, really appreciate it! I guess by performance i mean overhead like processing and loading records, executing reports, performing finds and just generally opening up and populating a layout/record. I think I also only explained half my problem (only realised it in retrospect). The system i inherited is really badly designed, even a novice like me can see it. Tables are massive (2000+ fields per table in some cases) and there is an incredible amount of duplication because of excessive lookups/calculation fields doing lookups. The system doesnt have many records, all in all I'd say about 10000. I've noticed that navigating to some layouts caused a slight 1 or 2 second delay before it actually loads up. This doesn't sound major, but for me its nuisance and just not good enough.

Now, the previous developer never created a seperate table for line items and simply added a grid with loads of fields with names like item1, item2, item3 and so on (terrible!). One grid (there are numerous) is 32 x 17 fields big.... So you can imagine where things are heading should extra line items be required as the company grows. The problem is the entire system is littered with calc fields referencing some of the totals and figures in these grids so changing it is going to be a huge task (unless you guys have any suggestions?).

Whats happened now is that a new module was added to the system where users need to reference to the figures in these grids. The fields of these "line item" grids are in TableA. The new module is based on TableB however (related to TableA). I could let the users for which the new module is intended get view only privileges to the layout and fields where the grids are and simply deny them access to all the other fields (which contains data they are not allowed to see). But this isn't ideal at all as they need to create new line items with reference to these figures which means they will have to jump back and forth between layouts to fill in data. So I'd like to display these grids on the layouts where the users actually work on to simplify things. And this is were im not sure whether I should simply use the table relationship to display the values (bearing in mind that its a lot of fields that will have to be processed or relookedup every time based on the relationship ) or simply lookup fields (which will cause even more duplication, but only a once off lookup which is fine as the values doesnt change and dont need to be relookedup). The performance might not be an issue right away, but im trying to prevent issues down the line as the system grows.

Sorry for the long post, thank you for your time! I appreciate it

[comment]

im trying to prevent issues down the line

The best way to do that, IMHO, would be to normalize the solution.

---

FWIW, I don't think displaying a bunch of fields from one related record should cause performance issues.

Edited July 20, 2011 by comment

[Ertjie]

Agree fully! I just shiver uncontrollably at the thought of wading through that mess to get things a bit more up to standard. I would prefer just rebuilding the entire system from scratch, but unfortunately I'm not the boss and so its not up to me. Just dont see the point of a relational database if its going to be used as some sort of mutated flat-file-relational-database-thing. But cool, thanks a mill for all the advice!