14 Replies Latest reply on Jun 14, 2017 10:10 PM by taylorsharpe

    multi eSQL  calc fields vs Single SQL and List()

      I was wondering if there is a preference for performance using multiple  SingleField Select eSQL calculation fields vs  A Single Multi-Select Field eSQL multi-select + Multiple List() calculation Fields

       

      My gut and limited understanding of filemaker made me believe that a single query + multiple native functions would have been less demanding than multiple queries of the same record record. It does not seem to be true... any confirmation or advice on best practices?

       

       

      an example of the two formats are listed below.   In practice I have about 6 calc fields at the moment

           

          

       

      ex.

      SetupcalcAcalcB
      calcC
      OriginaleSQL Select X where....eSQL Select y..eSQL Select Z..
      NeweSQL Select X,Y,Z Where...List (fieldA, 1)List(fieldA,2)
        • 1. Re: multi eSQL  calc fields vs Single SQL and List()
          Johan Hedman

          When you start using JOIN in your ExecuteSQL and have tables with a lot of data you should consider doing it with relationship or other FileMaker ways. ExecuteSQL is great to catch certain amount of data.

          • 2. Re: multi eSQL  calc fields vs Single SQL and List()

            TY and sorry for not providing more information.      originally I tried to use relationships in my original solution, but encountered some problems. Eventually, it was recommended on here that I use eSQL as a way to filter related records for flagged records achieve.  This solved the problem i was having, but I was skeptical of performance issues of multi queries.  

             

            Without going  into  too much detail the problem arose when trying to pull flag data only from a  single to multi record relationship. All native functions that i tried would only pull the first record only, but there are instances where there could be two related records whose results have been flagged.     Its  a science application dealing with experiment results.... in some circumstances the  tests are repeated as confirmation/validation or a backup sample is tested and compared to the original sample.

            • 3. Re: multi eSQL  calc fields vs Single SQL and List()
              philmodjunk

              What you describe seems a simple one to many relationship. There are many ways besides ExecuteSQL for effectively working with them.

               

              That's what happens when the words "without going into too much detail" often lead to. The lack of detail creates false conclusions on the part of people reading your posts. In database design, the details are critical.

              • 4. Re: multi eSQL  calc fields vs Single SQL and List()

                I apologize, but I chose not to go into the details in this post for that it was already established in another post. Felt it was redundant because a solution was provided by a member (though im sure there are better ways) after the recommendation using esql was made, no other comments were provided and the thread died. So I assume, since no additional input coupled with a working solution yeilded the most most effeicient way.

                 

                Lol not to mention that its a pet peeve of mine when multiple topics are included in a single thread.   ;-)

                 

                  I will link the OP, but im on my mobile atm. If there is a better and cleaner way I am definitely open to suggestions and appreciate any help provided. Thanks in advance and sorry

                 

                 

                Ps. Im still curious why  the original setup works better than the new...

                 

                Pull Flagged Data From Similar Records in Table

                • 5. Re: multi eSQL  calc fields vs Single SQL and List()
                  philmodjunk

                  Actually, you have a one to one relationship and no real need of executeSQL. Sre my reply in the other thread.

                  • 6. Re: multi eSQL  calc fields vs Single SQL and List()
                    fmpdude

                    SQL in some environments is rocket-fast. But, not, sadly, in FileMaker except for the simplest queries (without LIKE, GROUP BY, BETWEEN, ....) or queries with very little production data.

                     

                    OTOH, relationships in FileMaker (conceptually similar to a WHERE clause in SQL) are very fast. Native Finds in FMP are also very fast. Features like GTRR work amazingly well. These are chief among FileMaker's best features (Portals being another amazing FMP feature, but again, where there isn't too much data to slow them down).

                     

                    One of the major benefits of SQL in FileMaker (performance issues notwithstanding) is that you don't need to worry about a relationship existing between two tables. SQL will still work.

                     

                    Many of the FMP folks on this forum aren't SQL folks. Many of the folks on this forum are SQL folks. Hence, you'll get a lot of different views. That view diversity is one of the best things about this excellent forum!

                    1 of 1 people found this helpful
                    • 7. Re: multi eSQL  calc fields vs Single SQL and List()
                      bigtom

                      Depends on the query. It is generally fSter from my testing to do simple queries all in one. Adding JOIN slows them down. Also remember that the first query is generally slow and subsequent queries for the same data are super fast as the table data is already on tap and ready to use.

                       

                      there are cases when this nought nit not be true (PSOS), but...

                      • 8. Re: multi eSQL  calc fields vs Single SQL and List()
                        fmpdude

                        Unfortunately, in the real world, the first query is all that matters...

                        • 9. Re: multi eSQL  calc fields vs Single SQL and List()
                          bigtom

                          In my real world it is not uncommon to loop an eSQL query and sometimes it loops over 100k times. Depends on your world I guess.

                          • 10. Re: multi eSQL  calc fields vs Single SQL and List()
                            fmpdude

                            My benchmarking for both FMP Loops and ExecuteSQL (for all but the very simplest queries) are not encouraging for situations with either many loops or a lot of data.

                            • 11. Re: multi eSQL  calc fields vs Single SQL and List()
                              BruceHerbach

                              I have been working on a solution that has to duplicate a series of records.  In the script I use ExecuteSQL to pull all of the data to be duplicated into a variable.  Then loop through the list and create the new record set.

                               

                              By using Perform Script on server, this seems very fast.

                               

                              I did use a couple of tricks:

                              A custom function that has the data and the field name come back paired in the  SQL result.

                              A script that takes a line from the SQL result and turn all of the pairs into variables.

                              Set up field definitions so that a new record will populate if there is a variable name that matches the field name.

                              So creating the record sets the values for all fields.

                               

                              So looking at your issue,  having the SQL return a substantial list of records should be fairly fast, if you can come up with a way of parsing/using the result effectivly.

                              • 12. Re: multi eSQL  calc fields vs Single SQL and List()
                                taylorsharpe

                                My experience is that making one call and parsing out results to variables is faster in scripting, but for unstored calc fields, it is faster to have separate SQL calls for each field.

                                1 of 1 people found this helpful
                                • 13. Re: multi eSQL  calc fields vs Single SQL and List()
                                  beverly

                                  My preference is to never have unstored calcs with ExecuteSQL(), but that's JMHO.

                                  I prefer to Script a Set Field.

                                  Beverly

                                  1 of 1 people found this helpful
                                  • 14. Re: multi eSQL  calc fields vs Single SQL and List()
                                    taylorsharpe

                                    beverly wrote:

                                     

                                    My preference is to never have unstored calcs with ExecuteSQL(), but that's JMHO.

                                    I prefer to Script a Set Field.

                                    Beverly

                                     

                                    I get your general point of the caution with ExecuteSQL having performance issues.  One advantage of an unstored field calc over a Script with a Set Field is that unless you use PSoS, it is done at the client level, which generally has a slower performance than on the server where a field's unstored calc happens.  But since there is a lot involved in optimizing performance, I often have to test things multiple ways to see what yields the best performance.  One disadvantage of a field's unstored calc is that FM tries to continually re-evaluate and update the unstored calc whereas a script storing result once is not recalculated except when the script runs. 

                                     

                                    I look forward to a Devcon session on optimizing performance, which I hope will be on the schedule, to talk about such issues.  I may be interested in such things as is it faster to do an ExecuteSQL or to make a RESTful API call to the same server for the data.... hmmmmmm