Optimising fetch performance on HD tables

No-Code Platform
1

HI there, I need some input regarding the performance of fetches please.

  1. Can you confirm if it’s more performant to create an existing HD and use the fetch detail records action than fetching details by nwHeaderId?
  2. Can you confirm if it’s more performant to Loop a subtable which is a Subtable Main than do a direct fetch.

I have my assumptions, but would like confirmations for future reviews/implementation.

Thank you!

2

As with most performance questions, the answer is situational and not universal. The following is generally true, but it won’t always fit based on architecture, table size, table indices, database load, etc. Performance testing will give you the best answer for any given scenario and can take into account scalability and server and database conditions.

Header-Detail (HD) Structures:

  • Generally, there should be no direct performance difference between Fetch Detail Records (with an existing HD structure) and Fetch Records (with an nwHeaderId)
  • There are notable performance differences downstream of that first fetch. Usage of the HD structure means maintenance on the Header and Detail tables together, which is worse for performance. Having said that, usage of the full HD is necessary when working in the action block or passing the structure to/from another logic block with the same shape
  • If there is no need to define and maintain the relationship to a header table (because the logic only needs to interact with a detail table), it will be more performant to avoid the HD structure as it comes to table updates and triggers.

Subtables:

  • If your logic requires mutating records in a subtable, it is the best practice to use Loop Subtable at all times
  • When reading records, Loop Subtable and Fetch Records can have the same or different performance implications based on the scenario
  • Fetch Records can be configured to fetch over subtable indices, which will be faster than a blanket fetch. It also has the ability to limit and sort the returned records, which can make a fetch faster or slower depending on the sort
  • Loop Subtable does not have the ability to add filter expressions, a limit, or a sort. This means that any instance of Loop Subtable will loop all subtable records on a single record unless that loop is exited
  • This means that Fetch Records will almost always be faster when looking for a particular record, but again, it should not be used to mutate a subtable

Takeaways:

  • Fetch Records and Fetch Detail Records themselves are the same but have performance implications for downstream logic.
  • Fetch Records and Loop Subtable have different applications. Fetch Records will be more performant for reading data, especially if Subtable Main indices have been defined and querying for a unique record. Loop Subtable should always be used when mutating subtable records.
3

@ross.blackburn 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.

1 Like
4

This is a great clarification and gets at the hard-to-understand properties of subtables. Thank you for explaining the details here, @brendan.b !

This nuance is really what I was trying to hint at. If the logic already has a fetch to the parent record, the additional subtable retrieval in a single database trip is nominal. This means that adding “Loop Subtable” to logic that isn’t already retrieving the subtable but is retrieving the parent record adds slightly to the performance hit but still resembles a single database trip. Loop Subtable should be faster than fetching and adding a database trip, in this case.

On the other hand, logic that has no access to the parent record will always be faster performing a Fetch Records directly to the Subtable Main* as compared to fetching the parent record and then looping the subtable. This is what I was getting at in the original post and I believe what you are now describing.
*as long as there is no mutation

1 Like