Why would a calculated field be FASTER than an indexed number field?

1

I have this one calculation that is based off another calculation. I’m trying to speed it up, so I cached the first calculation manually, but for some reason when I run it on the indexed number field it’s SLOWER than the calc.

CleanShot 2025-12-19 at 09.20.00

Here’s the formula. When it’s set to use _cache it takes about 3 times as long as when it’s set to the calculated field.

Min(

If (🧑‍🎓Student::GraduationDate ;

WorkingDays (
🧑‍🎓Student::Accountability_Engagement_FirstDayOfClasses_InTimePeriod_c  ;
🧑‍🎓Student::GraduationDate
;
ExecuteSQL ( "SELECT (HolidayList) FROM Settings
"; "" ; "" )
)
;

WorkingDays (
🧑‍🎓Student::Accountability_Engagement_FirstDayOfClasses_InTimePeriod_c  ;
🧑‍🎓Student::Accountability_Engagement_LastDayOfClasses_InTimePeriod_cache
;
ExecuteSQL ( "SELECT (HolidayList) FROM Settings
"; "" ; "" )
)
)

; 180
)
2

Are you able to upload a stripped-down FMP12 file here containing only the tables & fields which exhibits the behavior? That’s probably the best way to get advice.

Questions/ideas:

  • You say it’s slower when use_cache is true, but I don’t see use_cache anywhere in your formula. Can you explain?
  • ExecuteSQL() is slow, and in this case you appear to be calling it twice for each student. Assuming that “HolidayList” is a list of holidays that does not change per-student, it should be much faster to handle this differently.
  • Can you explain what the WorkingDays() function does? I’m guessing it is a function that loops over a start date to end date, counting # of non-weekend days, and also excluding the dates in HolidayList ?
2 Likes
3

More ideas:

Assuming that your data is not updated in real-time (for example, you only need to update it once per day, or once per login, etc.) I would consider using the Triggered Auto-Enter-Calc Indexed Field technique.

In this design pattern, you set up these complicated calculation fields as a normal field with Indexing enabled. The calculation then goes inside the AutoEnter calculation. Be sure to also turn OFF the “Do not replace existing field value…” checkbox:

image
image910×792 66.5 KB

The calculation then looks like this:

Let(

//  additional fields which will re-trigger this calculation
[     
  myTrigger = T1:Trigger
] ;

// the formula  (replace this with your actual calculation formula)
  If(T1::StartDate ≥ Globals::MasterDate; Worker::MemberType; T1::MemberType)
)

Since this field is both stored and indexed, it’s blazingly fast.

If you need to update it, simply set any value to the trigger field (in this case, T1:Trigger ).

Since the data will not automatically recalculate, you need to think carefully about when to trigger the updates, for example:

  • a Script which runs on the server once per day
  • a button on the layout named “Refresh” which updates the found set
  • an OnLayoutEnter script
  • etc…
4

I figured it out!

Well I still need to optimize it, and I’ll start with the HolidayList (thanks for the tip), but…

Creating a new indexed field but not writing anything to it somehow doesn’t index it.

I made a loop that went through and set everything to “something” and suddenly it’s just as fast to use the cached field Accountability_Engagement_FirstDayOfClasses_InTimePeriod_cache as to use the calculated field Accountability_Engagement_FirstDayOfClasses_InTimePeriod_c

I just spent a 15 minutes starting to get a stripped down version and it’s still not there so I’m going to bail for now, but thank you for the HolidayList tip.

1 Like