3 Replies Latest reply on Oct 1, 2014 9:55 AM by Doug Staubach

    Measuring "time to compute" on a calculated field (which trigger?)

    Doug Staubach

      Title

      Measuring "time to compute" on a calculated field (which trigger?)

      Post

      Hello.

      One of the best things about FileMaker is flexibility. In fact, there are a lot of times when the 'correct' answer isn't clear, and that's where additional performance data can help.

      I have a simple layout with a text field A, and a calculated value B. Sometimes this calculation takes 30-45 seconds (per record) to complete. I would like to measure the time that it actually takes (down to the millisecond), so I can compare different methods and how they perform against each other. The obvious goal here is to try and optimize each field and function, and I'm testing native FileMaker Pro vs. FileMaker customer functions vs. ExecuteSQL functions.

      This "timer" would ideally record a start value when the contents of field A are changed/saved, and would then record a stop value when field B has been fully populated and the cursor becomes available to the user again (the instant that the mouse cursor changes from 'wait' to 'ready'). I am open to any method that works, whether it is based on scripts, custom functions, and/or global variables.

      I have successfully created a script trigger to record the start time, in milliseconds, using the function Get(CurrentTimeUTCMilliseconds). I can either store that value in a global variable, or I can write that value to a field in the same record that was being edited; either method is fine for me.

      My confusion is how to determine the "finish time". I've tried to create triggers based on various field-level events, but none of them seems to be accurate (no clear way to determine when the calculated field has finished recalculation).

      Has anyone else solved this problem, and if so, would you mind sharing your method here?

      Thanks,

      Doug Staubach

        • 2. Re: Measuring "time to compute" on a calculated field (which trigger?)
          Doug Staubach

          Hi Steve:

          I appreciate your response, but unfortunately, these instructions demonstrate how to calculate the timing of a script. (This is more common than trying to calculate the timing of a calculated field).

          The good news is that I found a way to solve my own problem. It doesn't require any scripts, and it doesn't require any custom functions, so it should translate well to older versions of FileMaker Pro. -- However, I am using one of the newer Get functions, which includes milliseconds, so these instructions might have to be modified for FileMaker Pro versions earlier than version 13.

          Anyway, for those who are interested, here is the solution I came up with (tested successfully):

          I have a calculated field "FirstNames" that contains the results of the calculation that I want to test. (Note that in this particular case, I wanted to compare two different ExecuteSQL statements to see which one was faster, but the same principle could apply to any calculation that you want to test.)

          Instead of creating scripts and looking for triggers to attach to those scripts, I simply set a global variable $$StartTimer at the beginning of the "FirstNames" calculated field (using a Let statement), and then I created a new calculated field which I called "Statistics", and that calculation sets another global variable $$EndTimer (again, using a Let statement). I decided to put the EndTime as the second line item in this field, immediately after a function that copies the entire contents of the "FirstNames" field to a memory variable. (Doing this forces the timer to wait until the field is fully populated.)

          Following is the calculated field called "FirstNames":

          Let ([
              VAR1 = "SELECT statements go here..."
             ;$$StartTime = Get ( CurrentTimeUTCMilliseconds )
          ];
             ExecuteSQL ( VAR1 ; "" ; "" )
          )

          Following is the calculated field called "Statistics":

          Let ([
              VAR1 = FirstNames
             ;$$EndTime = Get ( CurrentTimeUTCMilliseconds )
             ;$DUR = ( ( $$EndTime - $$StartTime ) / 1000 )
          ];
             "Time: " & $DUR & " secs --- Returned: " & ValueCount ( VAR1 ) & " items"
          )

          Keep in mind that the time results that you get can be greatly impacted due to "caching", so if you run the same test twice, it will take a lot less time (in my case, the initial query took 33 seconds, and repeating the same query took less than half a second).

          Hopefully other people can use this technique, and if anyone has ideas on making it easier, better or more accurate, please let me know!

          Thanks,

          Doug Staubach

          • 3. Re: Measuring "time to compute" on a calculated field (which trigger?)
            Doug Staubach

            Hi folks:

            Just a quick word of caution:

            The solution that I described earlier is fine for testing "down to the millisecond" speed of complex calculations in an isolated (test) table in your database. My solution doesn't provide accurate results if there is more than one calculated field displayed on the layout, and it only provides accurate results for the current record.

            If I can find a better way to do this in the future, I will come back here and update this post.

            Thanks,

            Doug Staubach