4 Replies Latest reply on Dec 23, 2015 6:04 AM by siplus

    Can I use ExecuteSQL for this query?

    fmdataweb

      I've used the ExecuteSQL function for a number of simple queries within a single table and it's working well so far. I now have a more complex query that I would like to see if I can use ExecuteSQL for as the native approach with relationships with multiple predicates is extremely slow and I'm interested in comparing the performance against ExecuteSQL.

       

      Here's my tables:

       

      Contacts - stores the Contact details. If a Contact is considered a Client we set the client field to "Yes". contactID is the primary key field.

       

      Tasks - stores the Tasks assigned to a Contact and a User. Tasks is related to Contacts by contactID = contactID and to Users by userID = userID. The taskCompleted field tracks whether the task has been completed (Yes or No).

       

      Users - stores the User details. userID is the primary key field.

       

      ContactsUsers - a join table between Contacts and Users. Each Contact can be assigned to 1 or more Users. Stores both the contactID and the userID.

       

      I need to do a number of queries, but here's the one I would like to tackle first if possible using ExecuteSQL. For a given user I need to count the number of Tasks for Clients (Contacts flagged as "Yes" for the client field) that have been assigned to them and remove any duplicate contactIDs. For example if John Smith had 10 records with his userID in the ContactsUsers table representing 10 different Contacts, and out of those 10 Contacts 6 were flagged as client = "Yes" in the Contacts table, and out of those 6 Contacts 4 had tasks assigned with John Smith's userID, then the result would be 4 (regardless of how many tasks were assigned to the 4 Contacts).

       

      I've been able to retrieve the unique number Contacts with Tasks assigned to John Smith using the following ExecuteSQL request:

       

      ValueCount (

      ExecuteSQL ( "SELECT DISTINCT contactID FROM Tasks WHERE userID = ?" ; "" ; ""  ; Users::userID )

      )

       

      but this doesn't take into account just the ContactsUsers assigned to John Smith and out of those just the Contacts with client = "Yes".

       

      I'm learning ExecuteSQL as I go so hoping someone with a bit more experience can advise if this is possible and how to format such a request - not sure if I need to use a JOIN here etc or something else?

       

      thanks,

      Steve

        • 1. Re: Can I use ExecuteSQL for this query?
          coherentkris

          As the complexity of any query, whether using fm native structures like the relationship graph or SQL, increases or the ammt of records involved in the query increases, so does the time it takes to execute.

          If you look at the research on what others have done with ExecuteSQL you will find that ExecuteSQL becomes less efficient than other methods pretty quickly. There were some significant performance gains in ExecuteSQL from FM 13 to FM 14.

           

          I would put a calculation in the tasks table that gets the contact type (client or whatever) state from contacts table and

          ExecuteSQL ( "SELECT COUNT ( DISTINCT contactID ) FROM Tasks WHERE userID = ? and contact_type =?" ; "" ; ""  ; Users::userID ; "client" )

           

          >>  DISCLAIMER - The pseudocode above was not tested with your data. Actual results may vary.<<

          • 2. Re: Can I use ExecuteSQL for this query?
            BillisSaved

            Good morning fmdataweb,

             

            I hope your day is going well. I'm curious, does every task in your solution have both a contact and a user?

             

            God bless,

            Bill

            • 3. Re: Can I use ExecuteSQL for this query?
              Mike_Mitchell

              As an expansion of Kris's point, if you commit the records prior to executing the query, it will significantly improve the performance.

              • 4. Re: Can I use ExecuteSQL for this query?
                siplus

                An extreme example, fresh from the oven:

                 

                ExecuteSQL("

                SELECT DISTINCT perf.\"Responsible ID\", doc.\"Signed I\" FROM PerformanceSQL AS perf JOIN DoctorSQL AS doc

                ON perf.\"Responsible ID\" = doc.ID

                WHERE IDPayment = ? ORDER BY perf.\"Responsible ID\"

                ";": ";";   ";Payment::IDPayment)


                Takes 12 seconds, while


                Set Field [Payment::gRespList; List(Payment_Performance::Responsible ID)]

                Set Variable [$RespString; Value:Substitute(List(Payment_ResponsibleList::CodeAndNameForTarmed);"¶";"   ")]

                Exit Script [Result: $RespString]


                takes 3 milliseconds.

                 

                 

                table sizes:

                 

                Doctors: 60 records

                Payments: 77'000 records

                Performances: 680'000 records

                 

                the result of both approaches is the same.