3 Replies Latest reply on Jan 29, 2013 12:06 PM by Mike_Mitchell

    Limits to Auto-enter triggers?

    BTimm

      I'm in the process of converting my solution from a heavy volume of old unstored calc fields to flat fields with auto-enter's linked to one central Trigger timestamp field. In one database, I'm already at the point where running the timestamp updates is much slower than it used to be, approx close to a full second to update each record. This will be an issue when I am looking to update triggers and cascading fields in batches of thousands. Also, for client pages on single records when I want to update trigger on record load, scrolling from one record to next will be user drag.

       

      So, yes, I'm looking to fine-tune the cascading and understand where I am building up the drag time. Would like to know more about how auto-enters work under the hood.

       

      Possibillities:

       

      - the sheer number of auto-enter fields triggered from one trigger field?

       

      - auto-enter calcs dependent on other unstored data? Or related table data? Or unstored by virtue of some related table data?

       

      I would like to improve my ability to fine-tune and accelerate the updates, and ferret out any lazy calcs here.

       

       

      Method I'm using

       

      Flat field (date, text, etc.)

       

      auto-enter, replace data

       

      Calc:

       

      Case(

      trigger=trigger;

       

      [actual calc];

       

      )

        • 1. Re: Limits to Auto-enter triggers?
          Mike_Mitchell

          Hello, BTimm.

           

          It's hard to tell without more information exactly where your performance drain is coming from, but there are some general areas you can look to when you're having issues with calculations.

           

          1) The width of the table. FileMaker's model is "record-centric", meaning when a client requests a record from Server, it gets it - all fields (with a few exceptions). So if your table has 30 fields, it will download each record considerably faster than if it has 300 fields. Strip out unnecessary fields.

           

          2) Dependencies. If field A depends on the results of field B, which depends on the results of field C, then FileMaker has to wait for each predecessor result before it can start evaluating the current result. This is called the "dependency tree", and it will kill the calc engine. Avoid writing calculations that do this, within reason.

           

          3) Use of aggregate functions. Because the client-server model is record-centric, FileMaker does some things to improve performance by default. For example, in Form View, it will download records in batches of 25, or in List or Table View, in batches large enough to display. It'll download additional records as needed. But when you use aggregate functions - like Count ( ), or Sum ( ), or summary fields, then FileMaker has to grab the entire found set in order to perform the calculation. Big slowdown, if the found set is large. (Same is true for related records, but in this case, it only grabs the records related to the current parent record.)

           

          4) Calculation inefficiencies. These can come from a few other sources, like repeating yourself or repeating yourself.  

           

          For example, say you want to calculate a sum of the line items of an invoice, and then add sales tax. You might write:

           

               Sum ( Line Items::Amount ) + 0.07 * Sum ( Line Items::Amount )

           

          Well, that would probably work ... except it means the calc engine has to calculate the sum of the line item amount twice. Instead, why not write:

           

               1.07 * Sum ( Line Items::Amount )

           

          Another example is the Case function. As an efficiency, FileMaker will evaluate Case until it hits a true condition, and then stop. So assemble your Case statements in the order they are most likely to occur. For example, if your most likely case is that an invoice is "Open", then create the Open case as the first one in the chain. That way, FileMaker only has to evaluate the first condition before it can quit.

           

          Or, perhaps you have unnecessary tests in your Case statements. For example, take a look at this:

           

          Case ( Amount < 0 ; "Negative" ; Amount >= 0 and Amount < 10 ; "First" ; Amount >= 10 and Amount < 20 ; "Second" ; Amount >= 20 and Amount < 30 ; "Third" ; "Bigger" )

           

          Notice anything? In each bracket, we have an unnecessary test. What about this one?

           

          Case ( Amount < 0 ; "Negative" ; Amount < 10 ; "First" ; Amount < 20 ; "Second" ; Amount < 30 ; "Third" ; "Bigger" )

           

          Both statements produce the exact same result, but the second one will process faster. FileMaker only has to evaluate a single condition for each test on the second version.

           

          These are just some things to look at. But maybe if you give us a little more info, we can give you more precise direction.

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Limits to Auto-enter triggers?
            BTimm

            Thanks, Mike.

             

            I can say it's unlikely to be aggregate functions or table width, since the area where I am seeing the drag is on an admin layout with minimal or none of either. I am pretty sure it is something within the calculation tree that I need to debug.

             

            I'm first looking for general issues with the auto-enter, so I don't have to bug everyone with the exact details of my calculations. Ideally, I can start with some of the info here and self-discover.

             

            My first hunch has been something in the dependencies area. Let's say I have dependencies 2 or 3 fields deep.  Some of which do involve related fields which would need to be calculated on the fly, but I have lately been piling up a number of additional flat fields to trigger. Not sure if the relateds are more of a factor or whether it's simply making one trigger update 10 fields instead of 5 is the issue. Likely both.

             

            Calc efficiency is certainly something to consider. There's a lot of fine-tuning tips in the semantics of calcs I haven't really considered and will keep an eye on.

            • 3. Re: Limits to Auto-enter triggers?
              Mike_Mitchell

              Just remember - the absence or presence of a field on the layout will only affect you if it's an unstored calculation. It affects nothing relative to the download of the table width.

               

              But you're likely right; if you have 2 - 3 layers of dependencies, including related fields, I'd start there.

               

              Good luck.

               

              Mike