5 Replies Latest reply on Oct 11, 2016 10:23 AM by Razor

    Time to Update 7,700 Records

    Razor

      As the scope of my project changed I recently added a field to one of my tables to record a vendor ID. Since it is a new field and there is only one vendor using the project I wrote a simple script like so;

       

      GTL

      GTR First

       

      Loop

       

      If IsEmpty(PackagedItem::Vendor_ID)  // I know I don't need this check but I got in the habit of using it just in case I ever made a mistake selecting a field, etc.

      SF [PackagedItem::Vendor_ID]; "the vendor ID"

      End If

       

      GTR Next

       

      End Loop

       

      This file exists on a remote server hosted at a reputable location. My table has ~ 7,700 records and it took 36 minutes to run. To me that seems like a lot of time for something relatively simple. Is that normal time for FileMaker?

        • 1. Re: Time to Update 7,700 Records
          coherentkris

          Alot depends on how complex the layout on which the script was performed AND how complex the table is.

           

          Good rule of thumb is to run data manipulation scripts as perform script on server when possible because server generally runs faster than client.

           

          Controls on the layout and calcs/lookups on the table = impact to performance.

           

          Run long scripts from "thin layouts" that is layouts with minimal controls.

           

          Also freeze window at the top of a script may speed things up.

          • 2. Re: Time to Update 7,700 Records
            mikebeargie

            Yeah, it takes longer because you are adding at least an extra 14,000 commands.

             

            If you just use:

            Replace Field Contents [ PackagedItem::Vendor_ID ; if ( IsEmpty(PackagedItem::Vendor_ID) ; "the vendor ID" ; PackagedItem::Vendor_ID ) ]

             

            Then you can do ALL of that in one script step, taking much less time.

             

            Essentially, right now, you are doing:

            7,699 go to record script steps

            7,700 "if" script steps

            x number of "set field" script steps

             

            If you index the Vendor_ID field, and change it to the single script step instead of a loop, then it should be much faster.

             

            You can make it even faster (seconds really) by using Perform Script on Server if the file is hosted in 13+ as well.

            • 3. Re: Time to Update 7,700 Records
              mikebeargie

              If you are using a hosted file and not using perform script on server, then it also is probably slowing down from "progressive loading syndrome". A phenomenon for large looping actions where the loop gets progressively slower as it executes since it has to follow the data flow order of:

               

              -Request record from server

              -Transfer record to client

              -update record on client

              -request commit action to server

              -return result of commit to client

              -return updated record to client

              -repeat ad nauseum.

              2 of 2 people found this helpful
              • 4. Re: Time to Update 7,700 Records
                mikebeargie

                One last note, related data will always slow down actions as well due to the above. Say for example you are referencing the Vendor_ID from another Vendors table to pull into PackagedItem, Not only is it pulled the PackagedItem record, but also the related Vendor record as well.

                 

                Other things that can slow down these kinds of actions are unstored calculation fields, summary fields (EG if you have a summary field on the layout you are using, it may update the summary before moving on in the loop). Those things in either the main or related table can cause loop actions to slow.

                1 of 1 people found this helpful
                • 5. Re: Time to Update 7,700 Records
                  Razor

                  Thank you for the info, it make sense. I changed the script and re-ran it.

                   

                  Yikes! 4 seconds!

                   

                  Learned a big lesson today, thank you.