6 Replies Latest reply on Mar 28, 2012 1:50 PM by HOnza

    Set Globals or Unstored Calculation for Relationships?

    eric

      Which is faster or better for setting the static side of relationships?

      1. Initializing globals in an open script
      2. Setting unstored calculation fields with static values
      3. Some other alternative

       

      Specifically, I have at least twenty relationships that are keyed with globals on the "one" side, and are all set with an on-opening-this-file script. But I imagine that everytime a client or web-browser (to CWP) opens this file, it has to set all these globals even when the session might not use any of those relationships. Would static unstored calculations be more efficient, and would there be any drawbacks to using unstored calculations instead of globals?

        • 1. Re: Set Globals or Unstored Calculation for Relationships?
          HOnza

          Which one is better depends on the situation.

          In general, doing something unnecessary is always good to avoid.

           

          Unstored calculation gets recalculated whenever FileMaker things its value might have changed.

          So if you're going to use the relationship multiple times then global field may be more efficient.

          Global field will also let you force the relationship to refresh.

          With global field you have full control over when it gets updated.

          On the other side, if you use an unstored calc, you can completely avoid scripting.

           

          If you worry about the overhead of setting the relationship keys unnecessarily in the on-open script then I would suggest you use global fields but don't set their values until you really need them.

          However, in that case you need to set a good and reliable rule how to find out when you need to set the key.

           

          You may also want to consider using stored calculation fields placed in a session table. One benefit of this approach is that the stored calc can be indexed, so when you connect tables A and B to the same key field, you can access the table A from table B and table B from table A through this relationship.

           

          I have created a simple test database to compare these three approaches. Using the word "test" as relationship key, looping through 100 local records, accessing 100 remote records and calculating Sum from them took 0.032 sec using global field, 0.1 sec using unstored calc, and 0.1 sec using stored calc when tested for the first time after opening the file. Then every following try took around 0.017 sec using either method. After changing the relationship key to a more complex calculation, using global field took only 0.03 sec, using unstored calc took 0.186 sec and using stored calc took 0.105 sec the first time after opening the file. Then every following try took around 0.016 sec using either method, probbaly due to caching.

           

          So using global fields is the fastest approach in most cases and the overhead of setting the global field is almost equal to the overhead of the calculation you have to evaluate to get the value you actually want to store into the field.

          • 2. Re: Set Globals or Unstored Calculation for Relationships?
            eric

            Thanks.

            The keys in my case are meant to be simple constants. E.g. _kf_ID = "ACT"

            There are a couple tricks to set globals permanenetly without scripts, but I don't really want to rely on those unless the globals are certain to be significantly faster and if I don't want every client and PHP connection to have to set all those globals everytime.

            • 3. Re: Set Globals or Unstored Calculation for Relationships?
              HOnza

              I think there is only one way to set global fields permanently - doing so while having the file open directly in FMP or FMPA.

              However, that may be sufficient reason for not using that, especially if you develop on a server.

               

              As you can see from my test, using globals can be 3 times faster even when using a simple constant, at least until the relationship gets cached.

               

              I have also tried globally stored calculation and it's as fast (slow) as the regular stored calc.

               

              The important thing is that using the relationship via global field is not 3 times faster only when accessing it for the first time. My test shows that the whole loop going through 100 records is 3 times faster. So it seems that what matters is whether the relationship is accessed for the first time from that particular record. The two approaches become equally fast only when accessing the same relationship again from the same source record.

              • 4. Re: Set Globals or Unstored Calculation for Relationships?
                DavidJondreau

                Why not do a check in your opening script to see if the file is being accessed by CWP, and if not, skip the global setting?

                1 of 1 people found this helpful
                • 5. Re: Set Globals or Unstored Calculation for Relationships?
                  eric

                  When developing on Server, you can convert a global calculation to a text (or other) global field, and the last calculated value will stick.

                  • 6. Re: Set Globals or Unstored Calculation for Relationships?
                    HOnza

                    You've got me! Wasn't aware of this one. Neat trick!