3 Replies Latest reply on Sep 18, 2014 11:10 AM by philmodjunk

    Reducing Time to complete script with multiple Omits and Finds

    sccardais

      Title

      Reducing Time to complete script with multiple Omits and Finds

      Post

      I have a file of Users - past and present - with over 30,000 records that is growing steadily. Each month, we use this file to send about 3,000 surveys to a random subset of this file. 

      Before choosing the random group of 3,000, we need to omit records from consideration based on - currently - 11 tests.

      I have a script that does this by performing a series of 11 tests in sequence using a two step process:  (1) Find (or Omit) test A,  (2) "Replace Field Contents" of field "OmitReason" with a description of the test it failed. Repeat this process for 10 more tests. In the end of this process, the field "OmitReason" for any single record might contain multiple reasons for omission such as  "Invalid email, < 90 days as customer, missing email address, etc.

      The script works but it takes about 20 - 30 seconds to complete. I'd like to know if there is a more efficient way to write this script. The current process seems like brute force.

      The process of creating a randomized list of 3,000 records based on the records remaining after the omission steps above is pretty straight forward so I don't think it's the reason the script seems to take so long.

        • 1. Re: Reducing Time to complete script with multiple Omits and Finds
          philmodjunk

          To be sure that it's the find, you could insert a Show Custom Dialog to pop up just before you do the randomizing part of this process.

          A frequent culprit for slow finds is to specify find critera in fields that are unstored and thus unindexed. Criteria in related records also slows things down.

          It would help if we could see the script that you are using.

          To post a script to the forum:

                 
          1. You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
          2.      
          3. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
          4.      
          5. If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
          6.      
          7. If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
          • 2. Re: Reducing Time to complete script with multiple Omits and Finds
            sccardais

            FYI ... Adding the custom dialogue was a great idea. One of the 11 finds is searching a field that is not indexed. The field, "SinceLastSurvey" is a calculation with Unstored number result Get(CurrentDate)-Surveys::c_Survey_Date.

            c_Survey_Date is a calculation with stored and indexed date result but from an external table on local drive which may be another reason for the slow execution of this script.

             

            • 3. Re: Reducing Time to complete script with multiple Omits and Finds
              philmodjunk

              Here's a trick for getting faster results when you need to specify criteria in an indexed field:

              Perform a find without specifying that criterion. Then return to find mode, specify this additional criterion and constrain the found set. Since the constrain only works on the records produced by the first find, this almost always results  in a much faster find.