1 2 Previous Next 26 Replies Latest reply on May 28, 2017 11:38 AM by LewisRobinson

    Nonlinear response to a script


      I have written a program which moves multiple Hypercard Records into a TextEdit file.  I have written a Filemaker script which takes that file, puts it into an empty field in a Filemaker table and then creates new records in the table for each Hypercard record in the file putting the data for that Hypercard record into a field.


      Number of records in TextEdit file   Size      Time for script to run

      100                                                 593 K      under 1 minute

      200                                                1.26 M    under 2 minutes

      400                                                2.46 M    under 3 minutes

      800                                                4.36 M     9 - 10 minutes

      A 10 megaByte file took over 2 hours


      Increasing the size of the file cache to 255 M has made no difference in speed.

      The program beeps after each record is processed and the beep frequency slows down markedly toward the end of the run.


      I'd be happy to put a copy of the script here, if someone can tell me how to do it


      Control-A turns the whole script blue, followed by Control-C, followed by Control-V does not copy the script over here.

        • 1. Re: Nonlinear response to a script

          print the script and save as a PDF, then post the PDF here

          • 2. Re: Nonlinear response to a script

            or more convenient for everyone:


            ... copy the text from that PDF and post the text here (after checking the text and applying some post-processing; usually a few line breaks get jumbled).


            btw, I think this only works on a Mac, where you can


            • open the script

            • press cmd-P

            • select "Open in Preview" in the Print dialog

            • in the new PDF window in Preview, copy the entire text

            • close the PDF window


            This way you don't even need to save a PDF.

            • 3. Re: Nonlinear response to a script

              Or if you have advanced, you can create a DDR and copy the script from your browser.

              • 4. Re: Nonlinear response to a script

                I did a test where I wrote a 20,000,000 loop in FMP script where each loop iteration computed a couple transcendental functions (sin, log, etc.). That FMP script took 1 hour, 19 minutes.


                The exact same logic in Java took 1.5 seconds.


                FWIW, that same script in C was so fast I couldn't measure it.

                • 5. Re: Nonlinear response to a script

                  I suspect you have something indexed, so as the list gets longer, it takes more time to perform.


                  Look at the table and see if any fields are indexed

                  • 6. Re: Nonlinear response to a script

                    Another possibility is you use GetValue(text ; n) function on very long text to get each line increasing n in loop.

                    • 7. Re: Nonlinear response to a script

                      user19752 wrote:


                      Another possibility is you use GetValue(text ; n) function on very long text to get each line increasing n in loop.

                      This is exactly what I suspect is the most likely issue. The time it takes for GetValue (or any other FileMaker text parsing function) to extract something from text is linear with respect to the position in the text. This linear cost is repeated for each value you extract because the functions have to start over at the beginning of the source text every time you call them; there isn't a cursor saving FileMaker's place in the middle. The longer your source text is, the more times you have to extract something, and the more it costs to extract each later piece. The total cost is quadratic (O ( n^2 )).


                      This can be improved to O ( n^1.5 ) by reducing the size of the source text at each iteration:



                           Set Variable [ $value ; Value: LeftValues ( $sourceText ; 1 ) ]

                           Exit Loop If [ IsEmpty ( $value ) ]

                           Set Variable [ $sourceText ; Value: Replace ( $sourceText ; 1 ; Length ( $value ) ; "" ) ]

                           # Do whatever you're going to do with $value

                      End Loop


                      The same principle should work with whatever other incremental parsing you might be doing: extract the next piece you'll work on, then chop the front off the source text. Using the Replace function to reduce the source text, and not something like Right or RightValues, is important. Credit to DavidJondreau for suggesting this technique in place of a more complicated "chunking" approach to O ( n^1.5 ) performance.

                      2 of 2 people found this helpful
                      • 8. Re: Nonlinear response to a script

                        This isn't your main issue, but a Freeze Window before you begin your loop will speed things up.  Also I have noticed that if the script workspace is open, this can slow down script performance considerably for some reason.


                        But when your java script is over 3100x faster there is clearly more going on than just that.

                        • 9. Re: Nonlinear response to a script

                          You may want to check the QuickList functions in MBS FileMaker Plugin: List.


                          There you can read a list into a QuickList via QuickList.New and query quickly via QuickList.GetValue. This is magnitudes faster then GetValue in FileMaker, especially when you have thousands of values.

                          • 10. Re: Nonlinear response to a script

                            Well, my Java "script" wasn't a script and that's kind of the point. It was ... compiled.


                            In my case, I didn't have the script window open. I had posted the simple looping script that took an hour and 19 minutes. This particular script had no database access was a bare-bones loop with two set variable script steps; it was all "in memory" and STILL took an hour, 19 minutes.


                            These are the kinds of non-functional issues (performance in scripts and SQL) that keep me from considering FMP for production uses - at least for my more mathematical applications.

                            • 11. Re: Nonlinear response to a script

                              Shouldn't having an index (and the program using it) .. save you time? I'm missing your point.

                              • 12. Re: Nonlinear response to a script

                                A picture is worth 1,000 words...


                                But, how many records were there in your 10MB file? Since you didn't specify the record size, I can't accurately graph the non-linearity with this final value. I made an approximation from your posted data, however.



                                • 13. Re: Nonlinear response to a script

                                  When doing data conversion like this, I think it is a good idea to take advantage of FileMaker's Import capabilities.


                                  Instead of loading up a single text field and then parsing the many records out of it with a script, you can format the text file you created with your original program into any one of the text file formats that FileMaker can import directly (XML, CSV, tab-delimited).


                                  Even if all that you are doing is to break up the original into individual records and import an entire Hypercard record into one field of a FileMaker record, you have put yourself into a much better situation for parsing a large number of records.  Your script would then loop over FileMaker records and parse out the fields for one record at a time.  It is doing many operations on a huge text field that is killing your speed.



                                  • 14. Re: Nonlinear response to a script

                                    To test the export/import method, as a proof-of-concept, I created a big string of random text that represents the text in your TextEdit file.  In my case I generated random text representing data from 2000 records.  Each record is between 2048 and 10240 characters long so the entire global text field is over 10M characters.

                                    To distinguish records, the text contains the string "___RECORD_END___" at the end of each record.


                                    To process this big sequence of text in a globally stored Text field into 2000 separate records, I used this 4-line script to

                                    1. prepare the text so that one line represents one record and there are no raw tab characters
                                    2. export to a tab-delimited file
                                    3. import each record in the tab-delimited file to a single field in a record in the target table


                                    Set Field[WorkingArea::gBigText; Substitute(WorkingArea::gBigText; ["¶"; "\¶"]; [Char(9); "__TAB__"]; ["___RECORD_END___"; "¶"])

                                    Export Field Contents[WorkingArea::gBigText; "exported.tab"]

                                    Go to Layout["Target" (TargetTable)]

                                    Import Records[No dialog; "exported.tab"; Add; Unicode]


                                    This took 5 seconds to run and create the 2000 records.  This is intended to do what your script does in 2 hours.


                                    Note that the Substitute() above will encode the line beaks in your records as ¶ characters and tabs as "__TAB__".  When parsing your records into individual fields, you can do the inverse Substitution(), i.e.


                                    Substitute(TargetTable::MyRecordText; ["\¶"; "¶"]; ["__TAB__"; Char(9)])




                                    1 2 Previous Next