Performance core principles: 1. Unstored calculations

Curated Collections NickLightbody's
1

1. UNSTORED CALCULATIONS

When are unstored Calcs useful and when should they be avoided?

It depends!

Unstored calculations can be useful and can be used as long as we are aware of what the limits of sensible application are. Used inappropriately they can slow execution to a crawl. The reason for this performance hit is that an unstored calculation will revaluate frequently and thereby create an unnecessary load which becomes noticeable on a more complex system and/or a system with more records and larger amounts of data especially when those effects are multiplied by network latency.

Types of appropriate use

GENERALLY: Use auto-enter fields rather than calculated fields whenever possible because they will only re-vevaluate when necessary, if you permit them to over-write existing values.

LOCAL or NEARBY: use calculation fields only for things in the local table, or one hop away for gathering lists, avoid using for aggregation of data.

DATA CACHING: a calculation field can provide, for example, a snapshot of the entire record data in JSON form. Such a field could hold data from related records forming a cache available for use without further communication with related records. In this case controlling the re-evaluation of the calculation is important. An auto-enter field may offer a more controlled method?

DATA DISPLAY: consider using a button-bar to display unstored calculated data without adding the field to the layout. Be careful that you aren't triggering unnecessary requests to the server for data you don't need. Remember that Button Bars and Repeating Fields resize proportionally.

CONDITIONALLY HIDDEN: consider displaying data in a hidden object using an unstored calculation because the calculation will only evaluate when you display the object providing some control of the load created. Use a Hide Object control, Pop-Over or an inactive Panel whether Tabbed or Sliding. However, since FM18 fields (excluding containers which are streamed only to what you can see) placed out of sight in the developer area to the right of the layout are calculated although they cannot be seen.

MERGE VARIABLES: consider using a calculation to set a merge variable to display data, perhaps from several fields, as a single object, or use merge fields. Local $var used in the UI remain record specific whereas Global $$var are not.

VIRTUAL LISTS: when creating virtual lists unstored calculations are handy if not essential.

Nick Lightbody
13/03/2021

Thanks to the following for their contributions:

  • Cecile
  • Torsten
  • jormond
  • Malcolm
  • mipiano
  • harvest
  • Vincent_L
  • Bobino
  • jwilling
  • steve_ssh
  • AndyHibbs
  • tonywhitelive
4 Likes
2

What differentiate calculation fields and unstored calculations (memory blank)?

3

A calculation field can be stored or unstored. The unstored type evaluates freely whereas the stored type needs more stimulus to reevaluate.
Can anyone explain exactly what the are the stimuluses that are required to make a stored and an unstored calculation field reevaluate?

1 Like
4

We are talking here specifically about fields.

Calculations can be made wherever the calculation engine interface is exposed (maybe list these?) but understanding and avoiding the inappropriate use of unstored calculations which slow down performance by creating a constantly recurring load is the subject here.

5

Can we move the current thread on when to use unstored calcs from the Lounge into this topic?

1 Like
6

@nicklightbody had the good idea of delving deeper into concepts and implementation strategies that enable high-performance solutions.

I am opening a separate thread for the calculation field topic, but consider @nicklightbody as the OP.

We all started out making extensive use of (unstored) calculation fields because they come in handy and we get a quick result. And we all made the experience that what works well for a couple or even a few hundred records, will not necessarily perform well with large data sets - specially when relationships are involved.

I admit to still using them in solutions with larger data sets, but not the way I did when starting developing in FM. Today, I am using them for displaying up-to-date data on layouts and there only for the parent table and/or a few related records in portals. A good example is a date/days calculation that involves current date or time.

They are not used for generating reports on larger data sets, as this surely slows things down to a crawl.

So (unstored) calculations can be useful and be used as long as we are aware of what the limits of application are.

How do you take advantage of (unstored) calculation fields?

4 Likes
7

A possible method of harnessing the convenience of unstored calcs but avoiding the cost is to have an unstored calc update an auto-enter field for the same value.

Years ago when I was removing unstored calcs from relationships I changed the original field so it became an auto-enter for the same value and then created a new unstored calc of that value beside it. It is possible to get an auto enter that doesn't immediately update when you want it to to update stimulated by an unstored calc. I haven't done this for quite a while, but it may be valid as a first step in improving an existing solution.

The issue of getting an auto-enter to update is normally solved by adding a tickler value which stimulates the update. Bruce Robertson suggested once when I was staying with him in Redmond that I just add something like

$tickler = getasnumber(get(currenttimestamp)) + 1 to the let () statement calculating the auto enter expression. It doesn't change the result but if this have to be evaluated it forces the whole expression to evaluate.

It's worth playing with and finding out what works.

Cheers, Nick

5 Likes
8

In one custom app, to improve performance in the inventory calculation side, I created a scheduled script that updated a set field with the current inventory for the previous day. Then the unstored calc only needed to look at data from that day, and the stored field. It dramatically reduced the hit from trying to calculate inventory from an untold number of records.

Eventually the move was to take the inventory calculations to using transactions. We can also reference the old daily set and calculate the current date inventory changes to verify the numbers are correct at any given time.

––––––––––––––––––––

Aside from that, we use calculation fields only for things in the local table, or one hop away. For the one-hop calculations, it's mainly for gathering lists, and not so much aggregation.

––––––––––––––––––––

For UI driven stuff, very often I will use a button-bar to format or concatenate text to make it more readable for the user, and avoid adding an underlying field. This requires careful use, so that you aren't triggering unnecessary requests to the server for data you don't actually need.

4 Likes
9

I think that I've moved all of my stored calculations to become auto-enter text/number fields. So that problem is solved and it leaves behind very few unstored calculations.

If the need for an unstored calc is for display purposes, then I'll move the calculation into the layout rather than use a field. Then the calc is only called when required.

If it is an aggregation that is required for a summary/report/archive then I will try to and then to generate a script which can run the equivalent calc and be triggered as required to update a static field.

For the on layout calcs that are time consuming, I'll have a hide-when criteria that can be switched on/off by script. Then I provide the user with buttons to show/hide the object. These switches are on (hide) by default so the layout only needs to do the calc on-demand.

4 Likes
10

@Malcolm I also use almost only auto-enter fields instead of calcs.

Otherwise, I also calculate everything that is possible via script.

The idea to move calculations into the layout, I find great! Cool thing!

1 Like
11

no calculation fields
no auto-enter apart from UUID-generation in ID fields

I produce my own bottlenecks by using conditional formatting in UI for colouring and highlighting

1 Like
12

How do you do this ? I mean how do you show a calculated value on the layout ? Button bars ?
Would this work in list view

13

@Malcolm I like avoiding creating a field if possible, but simply focusing on the part where you say "Then the calc is only called when required", I was under the impression unstored calc were not invoked unless they are required. Do you have some testing that demonstrates otherwise? Where an unstored calc would evaluate despite it not being required?

14

Button bars ?

:+1: I love love love using button bars to display layout calculations that otherwise don't warrant a dedicated field.

3 Likes
15

I have added expressions to set $$var within what is notionally “conditional formatting” - I wanted to adjust text sizes based on window width but that did create a reproducible crash in some circs in 16 which I reported and it was fixed pre-release. But I am not sure whether this was what was being referred to here? Clearly you can change $$vars using an expression anywhere you can access the calculation engine.

16

As long as you don't have a full bar of them and that the calculation is not a Execute SQL that checks for the total number of records of XYZ status... btdt :joy:

17

Regarding "only called when required", I should clarify that I meant that a calc on a hidden object is not performed until the object is displayed.

I'm not sure when unstored calcs are performed. Is the unstored calcs performed when the record is loaded or is it only performed when the field is included and visible on the layout?

1 Like
18

@Vincent_L

Button bars are a great tool for this because the text output can easily be displayed in the button label.

I also use text objects with merge variables. For that to work you place the calc in the "hide when" calculation box and pass back a zero or one according to your needs. This is also a way to have merge variables displayed in an always visible object.

If you want your merge variables to be record specific, use single dollar variables. They are evaluated within the context of the record. This means each record can display different values and it is compatible with a multi-window solution.

@nicklightbody

I use $$vars and $vars in the layout. For uses like language-specific labelling, $$vars are best. For record specific information, $vars are best.

2 Likes
19

Calculated button bars are extremely fast! They are perfect to use in portal rows or list view!

20

Very interesting thanks for that, I always thought only $$ variable could work on layouts

1 Like