1 Reply Latest reply on Oct 27, 2012 8:58 PM by Malcolm

    Find with unstored calculation is slow


      I have a Members table in which I need to determine current members.


      Current is derived from a Payments table. Each dues payment has an expiration date, and I use Max(Payments::ExpirationDate)>Get(CurrentDate) to set a CurrentCalc flag in Members.


      The problem is that when I find current members for the first time, the find takes about 10 seconds when the solution is hosted remotely. I believe that this is because the current calc is unstored (because it references a related table's field). I tried to use an autoentered calculation (CurrentCalc = 1) in a number field to speed it up, but it gave incorrect results. I don't know why the calculation did not update properly.


      I am in the process of updating this solution to FM12, and I'd like to get rid of annoyances like this in the update.


      Do you have a suggestion to modify the solution to speed this find up?



        • 1. Re: Find with unstored calculation is slow

          It sounds like the issue is network latency excerbating a problem that you could ignore on a LAN.


          How about performing a find on the payments table for ExpirationDate >= (insert the current date) then go to related records in Members, matching all records.


          Another method would be to have a script modify the "current" flag in members on a daily basis. Find all memberships that expire on that day and set the flag. When a payment is made set the flag. Then the "current" flag is always up to date. Finds would be fast because you are searching on indexed field not an unstored calc.