Optimising fetch performance on HD tables

@ross is correct about the HD case: using HD Structures represent a potential performance hit. However, they have significant data integrity benefits. Please see the discussion here for more details.

For Subtables, Ross is right that it’s situational. His default is wrong though; Fetch Records will almost always be faster when looking for a particular record is mistaken.

When a record is originally fetched, its subtables are (usually) populated with it. Once those subtables are in memory, looping them and including a Conditional to filter to the record you need is typically faster than a Fetch Records, which always represents a database hit. This only becomes untrue when the subtable becomes very large for the given parent record.

Sometimes the subtables are not retrieved with the original record. This is true if the “Fields” on the parent record fetch exclude the Subtable, or if the Logic Block determines automatically that it doesn’t need to fetch the Subtable. If that’s the case, a Fetch Records with a filter will be faster than modifying the logic block in a way that causes it to fetch the entire subtable. This is more opaque though and harder to optimize around.

More info about Logic Blocks automatically determining what fields to fetch

In general, Logic Blocks limit Fetches to only retrieve the fields that are used by the Logic Block, or the whole record if the whole record is used (e.g. if it’s passed along to another Logic Block call). If your Logic Block fetched the parent record itself, does not reference the subtable anywhere else in its logic, and never uses the full record then the subtable will be excluded from the original fetch and a Fetch Records action will be more performant than a Loop Subtable.