14 Replies Latest reply on Oct 8, 2015 2:12 AM by beverly

    Improve performance of eSQL aggregate function

    sccardais

      My file contains Paycheck data for each payroll period over the past 3 years.

       

      A calculation field c_SumByYear summarizes the amount of each check (GrossPay) and groups the total by year. The calc also rounds the GrossPay to remove decimals and replaces the commas between the fields with hyphens for formatting purposes.

       

      The field is currently configured as a global. The calculation works but it is painfully slow.

       

      Any advise on ways to speed this up would be appreciated.

       

      Substitute(

      ExecuteSQL ("SELECT paychecks.cyear

      , SUM (ROUND(paychecks.GrossPay,0))

      FROM Paychecks

      GROUP BY paychecks.cyear";"";"")

      ; ","

      ;"—-"

      )

        • 1. Re: Improve performance of eSQL aggregate function
          Vincent_L

          I don't think it will improve it  lot, but maybe a little faster (since less round to do) and the may be more exact, if you round outside the sum

           

          Substitute(

          ExecuteSQL ("SELECT paychecks.cyear

          , Round( SUM (paychecks.GrossPay),0)

          FROM Paychecks

          GROUP BY paychecks.cyear";"";"")

          ; ","

          ;"—-"

          )

           

          Unfortunately GROUP BY is terribly slow anyway, you can see my issue report here, and you could chime in here

           

          Filemaker's ExecuteSQL GROUP BY is terribly Slow !

          • 2. Re: Improve performance of eSQL aggregate function
            wimdecorte

            You should calculate this date just once and store it statically, no point in recalculating the same thing over and over... then your reports and searches will be much faster.

            • 3. Re: Improve performance of eSQL aggregate function
              sccardais

              Thanks for suggestion and link.

               

              For some reason, this query results in an error . e.g. ?

              • 4. Re: Improve performance of eSQL aggregate function
                Vincent_L

                argh, yes you're right, because, Filemaker limitation again, you can't manipulate aggregate function results

                you can'td do SUM(my filed)+5, I've been bitten once again by this "industry first"

                • 5. Re: Improve performance of eSQL aggregate function
                  rrrichie

                  I create "aggregate" tables for this.  Historicaly data won't change, and a nightly/monthly/yearly script can fill the aggregated table, then for the query do a union

                   

                  SELECT stuff FROM aggregate_table WHERE year < current_year

                   

                  UNION


                  SELECT your query WHERE year = current-year

                   

                  Just make sure the columns from both SELECTs are the same.

                   

                  If you have a LOT of data you can even aggregate until yesterday and only do today "live".

                   

                  Happy coding.

                  • 6. Re: Improve performance of eSQL aggregate function
                    miqaels

                    This may be quite naive on my part but I have found that occasionally, very slow execution of eSQL steps  were related to the field display on the layout being included for quickfind. When i switched that off, the SQL calculation became almost instant.

                    • 7. Re: Improve performance of eSQL aggregate function
                      beverly

                      Wow! Thanks for pointing that out, miqaels! When I was doing extensive testing, I was using fields that had this (quick find) turned off. However most of the queries were also scripted (set field, not calculations) and most of the queries were made in a table with calls to other tables (therefore the "fields" were not even ON the layout calling them).

                       

                      I still think this is a great tip and probably worth a try for someone needing to get more speed.

                       

                      Thank you again,

                      beverly

                      • 8. Re: Improve performance of eSQL aggregate function
                        wimdecorte

                        miqaels wrote:

                         

                        This may be quite naive on my part but I have found that occasionally, very slow execution of eSQL steps  were related to the field display on the layout being included for quickfind. When i switched that off, the SQL calculation became almost instant.

                         

                        I am not seeing that.  I have a SQL performance test file with 1,500,000 records.  Doing a SELECT on a layout with fields that are included for QuickFind is just as fast as doing that same query on a layout with the same fields with the QF toggle turned off for them...

                         

                        Can you publish some numbers of the difference in speed and perhaps what more info about your setup and layouts?

                        • 9. Re: Improve performance of eSQL aggregate function
                          taylorsharpe

                          One weird FileMaker quirk is that ExecuteSQL will work faster if the current record is committed.  This may not have been an issue for you, but when you can, commit the record before running an ExecuteSQL.  The reason this slowness happens is that FileMaker wants to take into account uncommitted information in your search and in doing so, it really slows things down. 

                          • 10. Re: Improve performance of eSQL aggregate function
                            coherentkris

                            Another weird quirk is that ExecuteSQL sometimes does not release record lock immediately (leaves records in an open state) when its done. I've been putting a revert record call right after any call to ExecuteSQL just to mitigate this issue.

                            • 11. Re: Improve performance of eSQL aggregate function
                              wimdecorte

                              coherentkris wrote:

                               

                              Another weird quirk is that ExecuteSQL sometimes does not release record lock immediately

                               

                              Can you expand on that?  The whole concept of ExecuteSQL is that it does not open or lock records so I wonder what is going on there.

                              • 12. Re: Improve performance of eSQL aggregate function
                                miqaels

                                I think I mispoke - I had remembered a slowdown that was relieved by turning off quickfind that was related to an execute SQL statement. So I went back to look at the details and here is the situation:

                                 

                                I have a smallish database I use to keep track of the finances of a small  LLC; there are only a little over 100 records to date. I have a set of calculated fields that use Execute SQL - Select(sum) to total receipts, expenditures and fees in order to calculate total revenue. I wanted a way to click on a record and get a list of just those records with either the same payee or payor so I wrote a small script that looked to see if the record was that of an expenditure (had a non-empty payee field) or a credit (non-empty payor field) and then used quickfind to select all the records with the same payee or payor. I noticed that the search was slow enough to bring up a progress box saying "searching records" - just for a 100 records! Then I found that the calculated summary fields described above were included for quickfind. When I switched that off, the searches became instantaneous.

                                 

                                So it seems it was not the Exceute SQL itself that slowed down but the quickfind step that somehow gets bogged down when it has to include a field that uses Execute SQL to display a result. Sorry if I misled anyone.

                                • 13. Re: Improve performance of eSQL aggregate function
                                  rrrichie

                                  Yes that would be interesting, do you think it would lock to keep users from changing data while doing the query?  I can imagine it might keep an uncommitted record "longer" and you are performing the executeSQL because of the point Taylor made.

                                  • 14. Re: Improve performance of eSQL aggregate function
                                    beverly

                                    well, maybe. But calculations? really?! Why not a script set field and/or auto-enter? The slow-down can be from recalculating these values in a list view.

                                     

                                    beverly