6 Replies Latest reply on Apr 7, 2013 7:58 AM by jbante

    ExecuteSQL problem

    ChrisPye

      I have recently started using the ExecuteSQL command in FM 12, and I'm really starting to see the benefits of learning/using this.. I have created a few simple queries that have saved me unnecessary table occurrences, bit I'm a bit stuck on a more complicated one.

       

      The two tables in question are "Company" and "Notes", joined by the CompanyID field in both.

       

      I need to get a count of how many related Notes for a Company were created between the current date and a pre-determined period before the current date (say 30 days). There is a date field in the Notes table named "Created". I'm aware of the SQL COUNT() and CURRENT_DATE function, but i'm not sure how to pull it all together.

       

      I know how to do this without ExecuteSQL but I would prefer use it if possible (and learn something new).

       

      Thanks,

      Chris.

        • 1. Re: ExecuteSQL problem
          jbante

          I'm presuming that you want to get the notes for a company one company at a time, as in you already have the Company record; please correct me if I'm misinterpreting. Does this do the trick for you?

           

          ExecuteSQL (

          "SELECT COUNT(*) FROM Notes WHERE id_Company = ? AND Created >= ?" ;

          "" ;

          "" ;

          Company::id ;

          $startDate ; // Get ( CurrentDate ) - 30, for example

          )

          • 2. Re: ExecuteSQL problem
            jbrown

            I use ExecuteSQL all the time. I use it for finds, but mainly to do big processing, to fill in number fields that were formally calculations which are usually* based on relationships. (I say usually because I'm sure someone here will disagree with me on that, so i used a generic term!) 

            • 3. Re: ExecuteSQL problem
              ChrisPye

              Thanks jbante, that worked a treat once I finally worked out that the Created field was actually set as a timestamp rather than date..

              • 4. Re: ExecuteSQL problem
                SBerger

                I found that ExecuteSQL is very slow when I want to get count of records.

                I tried it few times on table with 150K of records and it takes several minutes, as oppose of FM find that returns count immediately. Any thoughts ?

                • 5. Re: ExecuteSQL problem
                  BruceHerbach

                  I have done a couple of tests where I setup a script  to time a process that uses ExecuteSQL and compares the same Process using filemaker relationships or finds to get the same information.  For the most part it seems that in most cases the Standard Filemaker methods appear to be slightly faster.

                   

                  I have been finding ExecuteSQL to be very useful.  It does make it possible to simplify the relationship graph but it may not speed up a script.  That stated,  you can do some really neat things with it.

                   

                  Bruce

                  • 6. Re: ExecuteSQL problem
                    jbante

                    My experience with ExecuteSQL has been that you may need to experiment with different alternatives methods of getting the same result to get the most performant solution. Arranging join tables or where match conditions in a different order often leads to different performances. In one example with an outer join, the outer join with one ExecuteSQL query took in the neighborhood of 6 seconds; then I tried a separate query for each table and joined them with a custom function instead, which took about 20 milliseconds total.

                     

                    ExecuteSQL may be slightly slower than performing a find on a layout we're already on, but I've found it to definitely be faster on related tables — opening off-screen utility windows and going to layouts all take time, and usually more time than the difference between an ExecuteSQL query and a Find for me. For one especially performance-sensitive script I'm working on right now that retrieves data from several tables and sets data in several tables, it took between 1.5 - 2 seconds in a semi-WAN environment. By using ExecuteSQL (no layout switching or new windows) and collecting all record updates into a single transaction, it now takes 150-350 milliseconds, fast enough that the users' reaction times (and RDP latency...) are now the bottleneck. I don't care to make an equivalent version that works through relationships instead of ExecuteSQL, so I can't say how that might compare.

                     

                    There is one class of finds I've found where the ExecuteSQL method is definitely slower: partial word matches, i.e., anything using the LIKE operator. I presume ExecuteSQL does not get the benefit of FileMaker's word index, but I don't really know why.