1 2 Previous Next 24 Replies Latest reply on Mar 11, 2016 3:53 AM by wimdecorte

    Execute SQL runs slow 1st time it is executed

    Tom_Droz

      FM14ADV Windows 8, running on remote Server

       

      I have an execute SQL script that takes 10 seconds to run the 1st time I run it.  If I run it again it is lighten fast (under a second)

       

      Why does that happen and is there a way to make the 1st time run faster?

       

       

      Thanks

       

       

      Tom

        • 1. Re: Execute SQL runs slow 1st time it is executed
          alquimby

          Tom,

           

               My guess is that the first time you run it you are moving the data from the server database file to the work station file. The second time you run it that data is already present. My second guess is that if you close the file and re-open it the same thing will happen. Very slow execution the first time; faster thereafter.

           

          Al Quimby

          • 2. Re: Execute SQL runs slow 1st time it is executed
            Tom_Droz

            Allen, yes I can see that would make sense.

            • 3. Re: Execute SQL runs slow 1st time it is executed
              Mike_Mitchell

              Allen is correct. One thing you can do is to make sure you have no open records prior to executing the query. What this does is cause FileMaker to resolve the query on the server and deliver only the results, bypassing the caching issue. (Props to wimdecorte and his team for quantifying this.)

              • 4. Re: Execute SQL runs slow 1st time it is executed
                wimdecorte

                Mike's absolutely right about the open record: that forces FMS to send you all the data.

                 

                Other factors could be the complexity of the query.  Straight SELECTs are blazingly fast, but as you add more function calls or complexity (like LIKE) etc then the performance becomes gradually slower.

                • 5. Re: Execute SQL runs slow 1st time it is executed
                  Tom_Droz

                  I moved the script to a perform on server and that seems to make it very quick, thank you for the feedback

                  • 6. Re: Execute SQL runs slow 1st time it is executed
                    siplus

                    Yeah Tom, all my database files that heavily rely upon SQLs have a ExecuteSQL in the opening script. (Patients, payments, agenda, etc)

                     

                    Users tolerate a delay while all databases are opening - they get a global progress bar and are used to it.

                     

                    Once everything is up and running, the first use is fast and everybody is happy for the rest of the day.

                    • 7. Re: Execute SQL runs slow 1st time it is executed
                      Mike_Mitchell

                      In my testing, PSoS is actually slower than doing it locally, with committed records.

                       

                      Of course, if you need records to be open locally for some reason, then PSoS is faster.

                      • 8. Re: Execute SQL runs slow 1st time it is executed
                        rrrichie

                        I have had some pretty complex query's with multiple joins etc and they always take longer the first time (sometimes minutes) than the second time (sometimes down to seconds).  I've programmed most of them using PSOS and this behaviour is still the same, which I would expect cause if nothing changes, the caching mechanism will speed things up.

                         

                        The only time consecutive calls take the same time is ( in my experience ) if you have a result with a lot of rows (thousands), my guess would be that temporary tables or cached results don't fit in the cache anymore and get discarded.

                        • 9. Re: Execute SQL runs slow 1st time it is executed
                          wimdecorte

                          Mike_Mitchell wrote:

                           

                          In my testing, PSoS is actually slower than doing it locally, with committed records.

                           

                          Of course, if you need records to be open locally for some reason, then PSoS is faster.

                           

                          Again agreeing with Mike; if the open record can be avoided then you will get better performance locally than throwing this at PSoS.  Using PSoS carries other potential penalties:

                          - making sure your server is equipped to handle the extra load

                          - error trapping & handling on the client to verify that the server is not exceeding the max # of configured concurrent PSoS session

                          - ...

                          • 10. Re: Execute SQL runs slow 1st time it is executed
                            siplus

                            ... modifying the opening database's script to avoid doing things that should be done normally...

                            • 11. Re: Execute SQL runs slow 1st time it is executed
                              Tom_Droz

                              I am grabbing the IDs and some data on

                              the 30 last created records,

                              the 30 Oldest records that have a specific status

                              the 30 newest records that have a specific status

                               

                              I am throwing that data into a field in a utility table, then display that data to look like a portal

                               

                              (I was previously displaying in a portal but the performance was not acceptable)

                               

                              In testing this as a PSOS so far it seems very quick.

                               

                              This data changes throughout the day, so it cannot just be in an opening script.

                               

                              Not sure how to perform something like this without having an open record.

                              • 12. Re: Execute SQL runs slow 1st time it is executed
                                wimdecorte

                                Tom_Droz wrote:

                                 

                                Not sure how to perform something like this without having an open record.

                                 

                                Only an open record in the target table(s) by the user's session would make it that slow.  Doesn't matter if other users have open records or if you have an open record in some other tables that you don't target through the SQL queries.  So assuming that you are on a dashboard table, then it does not matter that you have an open record in that table.

                                 

                                Other than that, we'd have to see the SQL query itself to see if we can make it faster for you.  How do you handle the "30" cutoff?

                                • 13. Re: Execute SQL runs slow 1st time it is executed
                                  Tom_Droz

                                  Wim

                                   

                                  The data is displayed and refreshed alone side the records, so I don't think there is a practical way to have no open records

                                   

                                  Here is a visual  (the left is not a portal it is button with calculations, then when clicked goes to the record selected)SP.PNG

                                   

                                  The SQL example for one query is (GFN and GTN are custom functions)

                                   

                                  ExecuteSQL (
                                  //sqlQuery
                                  "SELECT " & GFN ( Base_Table_CONTACTS::__kp_Contact_ID )& ", " & GFN (Base_Table_CONTACTS::Display_Header)&
                                  " FROM "  & GTN ( Base_Table_CONTACTS::__kp_Contact_ID ) &
                                  " WHERE " & GFN ( Base_Table_CONTACTS::Type)& "= 'Prospect'"   &
                                  " ORDER BY " & GFN ( Base_Table_CONTACTS::z_RecordCreationTimestamp ) & " DESC" &
                                  Case($$VehicleOffSet>0;" OFFSET "  & $$VehicleOffSet*25  & " ROWS ") &
                                  " FETCH FIRST 30 ROWS ONLY"

                                  ;
                                  //fieldSeparator
                                  " | ";
                                  //rowSeparator
                                  "¶" ;
                                  //arguments...
                                  Date ( 1 ; 1 ; 1970 )
                                  )

                                  • 14. Re: Execute SQL runs slow 1st time it is executed
                                    wimdecorte

                                    Ok, the query itself is not the issue, it's fairly straightforward.

                                     

                                    And it's not a dashboard table, more like a dashboard widget.  One small suggestion: hide the widget on a popup so that users need to click it to see it, then you have control over the open record and you can commit before running the query.

                                     

                                    You can probably do that now, not sure how you currently update the portal on the right; triggers?  user action?

                                    1 2 Previous Next