4 Replies Latest reply on Nov 9, 2012 11:38 AM by jbrown

    Does ExecuteSQL need a warm up run?

    jbrown

      Hey all,

      I've noticed that the first time an ExecuteSQL statement is run to collect studentIDs for writing new records in other tables, that script takes a long time. The first time it takes a long time, the 2nd, 3rd, and other times it runs instantly. Why does it need to 'warm up' first?

      Let me describe my situation:

      The teachers at our high school see a list via a portal of the kids who are in the class, each period through a portal. The teacher uses a IncHmk_Boolean field to mark the students who have incomplete homework. At the bottom of the screen is a button that uses an executeSQL statement to collect all the IDs of the students who have that boolean field checked. It then writes that list to a new table for updating later on.

       

      All my teachers report that the first time they press the button it takes 20-30 seconds to process, that is to collect the student IDs. Going to a new class, they do the same thing, and the script runs in a heart-beat or less.

      The script collects the IDs through the executeSQL statement into a local variable.

      Ive noticed this happening in the script debugger as well. The first time i run the script, the debugger hangs on the ExecuteSQL statement. If I make a change to the script and run it again through the debugger, the script sails through the SQL step, writing the correct data to the variable.

       

      Has anyone noticed this? Why would ExecuteSQL need a warm up? Does it have to do with the TO that its pulling from? Do more WHERE conditions slow it down?


      Thanks

        • 1. Re: Does ExecuteSQL need a warm up run?
          ch0c0halic

          This sounds like a hosted solution. The first time FMP must query the Server for the data needed to perform the ExecuteSQL() command. The second time it is performed the data is in cache or the temp file.

           

          The number of WHERE clauses does not dramatically affect performance. More can sometimes improve performance.

           

          In addition, if the query gathers or uses data from more than one table you must include a JOIN statement to tell FMP explicitly what relationship to use. The ExecuteSQL() function does not even know about the TOG's let alone use them. I have found that adding the required JOIN can improve performance considerably (at least 2X).

          • 2. Re: Does ExecuteSQL need a warm up run?
            jbrown

            Hey,

            That completely makes sense. I had not remembered the caching of data.

            I'll try the join, although there's no real join in this. The ExecuteSQL statement does this:

             

            SELECT StudentID

            FROM EnrollmentTable

            WHERE IncHm_Boolean = ?

            AND Teacher = ?

            AND Class = ?";"";""; 1;$Teacher;$Class)

             

            Would this benefit from a join? Would that make it faster?

            • 3. Re: Does ExecuteSQL need a warm up run?
              ch0c0halic

              This query doesn't use more than one table so a JOIN is not needed.

              • 4. Re: Does ExecuteSQL need a warm up run?
                jbrown

                That's what i thought. Is there a way to speed up the cache of the data?  People are very happy that this has been sped up, this process, but that first one they're still reporting that first query is slow.

                Could I do a pre-query to force the cache before they go to enter this info?