9 Replies Latest reply on Feb 10, 2015 2:24 PM by Fred(CH)

    Search on unstored calcs

    Fred(CH)

      Hello,

       

      Yesterday, i solved an issue that i first believed very difficult and was finally very easy to solve.

       

      I inherited a very complex hosted database, in term of structure but also about the domain i have pain to understand : building  accounting.

      The database have multiple files (5) and each files have many tables.

      But overall, they have numerous unstored calcs : most of the calcs ARE unstored.

       

      Yesterday, the enduser complained about the tremendous slowness of a so simple script.

      With the debugger and a bit of analysis, i realized that the culprit was an unstored calc involved on search request :

       

      Enter Find Mode

      Set Field [<regular field 1>]

      Set Field [<regular field 2>]

      Set Field [<unstored calc 1>]

      Perform Find

       

      Instinctively, I first tried to understand why the calc has been unstored and also why it was so slow, to have a chance to optimize it.

       

      But  after 15 minutes to analyse the formulas and the relationships, i thought : no luck, except if i would rewrite the whole database.

       

      5 minutes to dream of a miracle and i had a very basic idea : perform the search in two steps instead of one :

       

      Enter Find Mode

      Set Field [<regular field 1>]

      Set Field [<regular field 2>]

      Perform Find

      Enter Find Mode

      Set Field [<unstored calc 1>]

      Constrain Found Set

       

      Yes, the script is now executing instantaneously.

       

      My conclusion :

      I really hope this gentle history could help beginners or intermediate on FileMaker scripting.

      But also, it could ask a more advanced question : could the FileMaker Search Engine be easily optimized ?

       

      Bye, Fred

        • 1. Re: Search on unstored calcs
          gdurniak

          It would be interesting to see what FileMaker does under the hood

           

          and if there is a query optimizer at all

           

          greg

           

          > could the FileMaker Search Engine be easily optimized ?

          • 2. Re: Search on unstored calcs
            srzuch

            Nice tip.  I guess FM only needs to re-calc the records initially found.

             

            If the find on the regular fields does not reduce the size of the found set (e.g. returns almost all the records), does the constrain find happen instantaneously?

             

            Steve

            • 3. Re: Search on unstored calcs
              flybynight

              Great tip. It's little things like this that totally make sense, when you think about them… but are often overlooked.

              I'm assuming that the first find narrows the found set quite a bit, to make the speed difference. It would be interesting to see the numbers for the total records vs how many in the found set after the first find. Also, what the speed would be of doing those in the reverse order (I'm assuming much slower, but curious how it would compare to the original script).

              Thanks!

              -Shawn

              • 4. Re: Search on unstored calcs
                Fred(CH)

                Obviously, this workaround worked fine in this particular case, because the first request reduced a lot the found set and then, FileMaker had a lot less record to recalculate when performing the second find...

                 

                But i hope / believe it match with a lot of case in real life...

                 

                Fred

                • 5. Re: Search on unstored calcs
                  GordonShewach

                  Thanks for your tip, Fred. I discovered this improved functionality a version or 2 ago in FileMaker and was thrilled. But I've found that simply combining criteria in stored fields with criteria for unstored fields in your find (your first example where you found things were slow) you can often get the same performance. I've never figured out which circumstances make your first example work and which circumstances require your 2nd example.

                   

                  Furthermore, I've encountered places where neither work (too big a table? too complex a calculation in the unstored field?). What I've done then is perform a find on stored fields then loop through the found set testing the unstored field to see if it matches a given criterion and omit it as needed. Or perform a find on stored fields, sort the found set by the unstored field, and loop til you find the point at which all other records should be omitted and do an "omit multiple."

                   

                  Gordon

                   

                  Example 1:

                   

                  Enter Find Mode

                  Set Field [<regular field 1>]

                  Set Field [<regular field 2>]

                  Set Field [<unstored calc 1>]

                  Perform Find

                   

                  Example 2:

                   

                  Enter Find Mode

                  Set Field [<regular field 1>]

                  Set Field [<regular field 2>]

                  Perform Find

                  Enter Find Mode

                  Set Field [<unstored calc 1>]

                  Constrain Found Set

                  • 6. Re: Search on unstored calcs
                    Fred(CH)

                    Thanks and very relevant questions !

                     

                    The first step found only 1 record from a table from (only) 315 record !

                     

                    The one step find took approximatively 10 seconds wether the two steps find seemed instantaneous.

                     

                    If i inverse the order of the two steps ? well i cannot say because i am away from the customer but i think it should be similar to the one step find...

                     

                    Fred

                    • 7. Re: Search on unstored calcs
                      imarc

                      One clue I've had that Constrain and Extend Found Set are not much more than wrappers for Perform Find (much like Go to Related Record): when assigning a script to the "Perform Find" menu item via Custom Menus, I quickly discovered that it broke everyone's use of Constrain/Extend Found Set menu items because those menu items were running the script as well even though the script had not been specifically assigned to them in Custom Menu Setup.

                       

                      The solution was simple enough: I assigned those menu items to the same script and passed "constrain" or "extend" as parameters, then branched those parameters off in the script to perform the single Constrain/Extend script steps. But before the need presented itself, it never would have occurred to me that I'd have to do that.

                       

                      Marc

                      • 8. Re: Search on unstored calcs
                        imarc

                        Furthermore, I've encountered places where neither work (too big a table? too complex a calculation in the unstored field?). What I've done then is perform a find on stored fields then loop through the found set testing the unstored field to see if it matches a given criterion and omit it as needed. Or perform a find on stored fields, sort the found set by the unstored field, and loop til you find the point at which all other records should be omitted and do an "omit multiple."

                        The toughest finds to optimize, in my experience, tend to be when you are trying to go to related records where both the found set and the target table are very large (tens of thousands of records). I've done the loop-and-omit trick before (though not the sort-and-omit-multiple trick, I'll have to try that one).

                         

                        Marc

                        • 9. Re: Search on unstored calcs
                          Fred(CH)
                          One clue I've had that Constrain and Extend Found Set are not much more than wrappers for Perform Find (much like Go to Related Record): when assigning a script to the "Perform Find" menu item via Custom Menus, I quickly discovered that it broke everyone's use of Constrain/Extend Found Set menu items because those menu items were running the script as well even though the script had not been specifically assigned to them in Custom Menu Setup.

                          Very interesting... As you certainly seen, my english is very poor. But if i understood correctly your comment above, you are concluding that we cannot expect any "optimization" of the search engine at this level. If so, you are probably right...

                           

                          I think i understood the strange behavior of custom menu you talked about, and it firstly sounds like an bug. But as you pointed out, it maybe reveals the ADN weakness of search engine.