4 Replies Latest reply on Aug 2, 2017 11:59 PM by mz5005

    Theoretical questions about scripting and performance

    mz5005

      I have a large (=many records) table A and a small table B.

      I want to compare all values of text-field X in A with those in B.

       

      Two ways of course:

      1. Value A1 search in B, value A2.. etc or

      2. Value of B1 search in A etc.

       

       

      1 means more, but smaller searches

      2 means fewer larger searches

       

       

      Q1 Is there any reason why scripting-wise one of the two methods is better?

      Q2 Is there any (theoretical) difference in time-to-finish?

       

       

      All input helpful!

        • 1. Re: Theoretical questions about scripting and performance
          wimdecorte

          You may not have to do as many searches as you expect from your description:

           

          - collect all distinct values from table A and all distinct values from table B (the indexes - different methods for doing this: ValueListItems, a ListOf summary, an ExecuteSQL() with DISTINCT,...)

          - do a FilterValues of both to get a list of what both have in common

          - do an anti-FilterValues of that common list against the list of table B and you'll have everything that is in B but not in A

          1 of 1 people found this helpful
          • 2. Re: Theoretical questions about scripting and performance
            danielfarnan

            My first question would be why you don't simply create a relationship on the relevant fields - what you are describing sounds like exactly the problem relational databases were created to solve.

             

            However, given what you have posed I suspect that process two will end up faster - you will be processing a smaller number of records starting from table B to gather your set of search terms, and provided the field in table A is indexed it will be faster to get a subset of records from that table.

            • 3. Re: Theoretical questions about scripting and performance
              TomHays

              I think option 2 of searching a large table fewer times will result in a faster total execution.

               

              "Q1 Is there any reason why scripting-wise one of the two methods is better?"

               

              The scripting engine is pretty fast (when compared to a human doing the same work), but the calculation engine is usually much faster.

              FileMaker Find operations are designed to be fast and to scale well with higher numbers of records.

               

               

              This is the kind of thing that is fairly easily benchmarked in a prototype database with sample data structured to represent your situation.

               

              The results may also depend on more factors than the two options you described.

              Is this a local database or client/server?  LAN vs WAN?

               

              There are even potentially faster approaches than what you described.

               

              -Tom

              • 4. Re: Theoretical questions about scripting and performance
                mz5005

                thanks wimdecorte  , danielfarnan, TomHays

                 

                @Daniel - good question. i have done that (in the real situation), but because of many variances in spelling (IBM Canada, IBM-Canada etc), comma's, and even character sets (these special characters on top of letters etc), I am still left with the described problem.

                 

                @Tom - Clear explanation, I will stick to the calculation engine the as much as possible.

                 

                @ Wim - that sounds like an intelligent solution. I understand what you are doing roughly, though never used Filter Values - will look into it. Thanks!