4 Replies Latest reply on Aug 4, 2017 12:52 PM by druide

    ESS/ODBC is slow when using calculation fields

    druide

      Hi,

       

      We are looking at moving data from a couple of FMP tables to MySQL, while keeping FM as the GUI. So far, everything is fine, and performance is good... except for one place. One of the layouts is using calculation fields to make it easier to search multiple fields from the same text box. Example:

       

      adresseligne1 & " "  & adresseligne2 & " "  & adresseligne3 &   " "  & adresse_secondaire_ligne1 & " "  & adresse_secondaire_ligne2 & " "  & adresse_secondaire_ligne3
      

      When we use those for search, FileMaker is fetching *all* data from the MySQL table, and since we have over 450 000 records, it takes a long time. If we use regular fields, everything is fast. But that would require to add many fields to the search layout.

       

      Any workaround?

        • 1. Re: ESS/ODBC is slow when using calculation fields
          mikebeargie

          Use a global field for the search box, then add a script that searches the same value for multiple fields, like so:

           

          Set variable [ $query ; sometable::globalfield ]

          Enter Find Mode [ no pause ]

          Set Field [ adresseligne1 ; $query ]

          New Record/Request/Page

          Set Field [ adresseligne2 ; $query ]

          New Record/Request/Page

          Set Field [ adresseligne3 ; $query ]

          etc...

          Perform Find

           

          No need for a calc field then.

           

          Alternatively, create a new field on the MySQL side and store those values with whatever is writing that data to the MySQL database. Then you have something that is indexable for searching.

           

          Another alternative could be an Execute SQL (script step) statement that updates the field in MySQL to populate a non-calculated field.

          • 2. Re: ESS/ODBC is slow when using calculation fields
            taylorsharpe

            Mike's advice is really good.  FM calculation fields on a MySQL table make it have to think through and calculate the entire database for that calculation field if you search on it.  You really want to avoid searching on a  FM calc field on a MySQL table unless the file is small.  Use Mike's suggestion or ExecuteSQL with PSoS and parse out the result. 

            • 3. Re: ESS/ODBC is slow when using calculation fields
              planteg

              Hi,

               

              out of curiosity, why are you moving the data to MySQL ?

              • 4. Re: ESS/ODBC is slow when using calculation fields
                druide

                planteg a écrit:

                 

                Hi,

                 

                out of curiosity, why are you moving the data to MySQL ?

                We are moving to an internal Web system. Moving out the data is the first step, so that other systems can read/write the data. Later, we will simply ditch the FileMaker GUI.