Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Speed of Summary Calculation Lookup Referenced Fields


Feirefiz
 Share

Recommended Posts

Reading posts here and there about the speed of various types of fields and operations (processes?), I started to wonder why in particular one is faster than the other:

 

calculation vs. summary : Is this absolute, or would it also depend on what's in the calculation? Would a calc field be faster than a summary field even if it referenced the same number of fields?

 

lookup vs. field several tables away in a Find: Is it because the lookup from a field several tables away occurs at record creation or some other point other than the Find itself? I.e. is it a displacement of the time it takes to another operation? Or is it in itself faster?

Link to comment
Share on other sites

calculation vs. summary : Is this absolute, or would it also depend on what's in the calculation? Would a calc field be faster than a summary field even if it referenced the same number of fields?

 

You are rarely actually in a situation where you can use either one in the very same table. Generally, you use summary fields to count aggregate data for a report showing records from that table, whereas you use calculation fields — in particular calc fields with Sum (Relationship::Field) or Average (Relationship::Field) —*most often from outside the table, with your "feet" planted on a layout and in a related table, such as the parent table (summing up the invoice line items for an invoice, summing up the daily records to get monthly totals, etc).

 

lookup vs. field several tables away in a Find: Is it because the lookup from a field several tables away occurs at record creation or some other point other than the Find itself? I.e. is it a displacement of the time it takes to another operation? Or is it in itself faster?

 

It is in and of itself zillions of times faster. If I define you as a "lookup" of your parents' holiday traditions, I can ask you directly when I'm standing right in front of you, and you answer immediately. If instead I reach through your relationship to them by having you drive back to their home to ask them, and then drive back, that's nowhere near as efficient, right? It may be more accurate (especially for any habits of theirs that may have changed since you grew up and left home) but it's way way slower, so if I can, I'm going to ask you instead. Works the same way for FileMaker relationships. A lookup field is a local field, even though it has inherited data from the table from which it looks data up. And because it is a local field it can be indexed. Fast finds — and fast sorts too. But a related field even one table away (let alone several tables away) isn't local and therefore there's no way to index it from here, from this vantage point. So it's much much slower to execute that Find (or Sort) operation.

Link to comment
Share on other sites

Oh yeahhh: the indexing part of the lookup! For the rest, I thought to myself, wouldn't getting the info from the other table to store in the local table (one time operation) take just as much time as going to get the info at a later needed point – the Find – if you only needed to perform the Find once? Like taking into account that I have to drive out to you in the first place for you to ask my about my parents' holiday traditions. Hm: but having to drive back and forth to ask them (reaching through the relationship) would mean two ways: already double the time. I'm not sure if that's overextending the analogy, but in any case I find the analogy quite refreshing at the end of a tiring day. Thank you!

Link to comment
Share on other sites

Well, also, a lookup isn't something that happens at the time that you request the info by performing a Find on the field. It has long since taken place, originally occurring probably at the time that the local record was created or, if not, then when some value in that record was modified in such a way as to trigger a new lookup. Either way, the Find operation does not need to do any fetching, the value is already just sitting there as a local field value, and that much would be true even if it were NOT already indexed.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use