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

    Find with unstored calculation is slow

    doughemi

      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?

       

      --Doug

        • 1. Re: Find with unstored calculation is slow
          Malcolm

          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.

           

          Malcolm