1 2 Previous Next 15 Replies Latest reply on Jun 7, 2017 4:34 AM by fmpdude

    ExecuteSQL & date ranges

    DavidZakary

      ExecuteSQL newbie here.

       

      Trying to get a count of records that occur between two dates.

       

      The start and end dates reside in global fields in a different table from the data being queried.

       

      The ExectuteSQL statement works fine without the date range. As soon as I put the range in it failes.

       

      My ExecuteSQL statement is as follows:

       

      SELECT count (*) FROM Usage2 WHERE Usage2::Usage_Date BETWEEN decay3::g_date_start AND decay3::g_date_end

       

      Any pointers?

        • 1. Re: ExecuteSQL & date ranges
          coherentkris

          ExecuteSQL ( "SELECT count (*) FROM Usage2 WHERE Usage_Date BETWEEN ? AND ?" ; "" ; "" ; decay3::g_date_start ; decay3::g_date_end )

           

           

          might work but is not tested by me.

          This should tell ExecuteSQL to use the globals as parameters in the query.

          You might also have cast the date fields as DATE() data type in SQL to get them to match.

          • 2. Re: ExecuteSQL & date ranges
            wimdecorte

            SQL expects the dates in a certain format so it is better that you feed the dates in as parameters and let FM take care of the proper formating and quoting

            There have also been reports that using normal operators instead of BETWEEN works better

            also you can not use FM field references (with ::)

             

            ExecuteSQL(

            "SELECT count (*) FROM Usage2 WHERE Usage_Date >= ? AND Usage_Date <= ?" ; decay3::g_date_start ; decay3::g_date_end

            )

            • 3. Re: ExecuteSQL & date ranges
              DavidZakary

              Getting closer. Wim's example is returning a zero. Better than the previous "?".

               

              Gotta learn more SQL.

               

              Thanks all

              • 4. Re: ExecuteSQL & date ranges
                beverly

                1. NEVER use COUNT(*) - what are you counting? It can take longer for all fields instead of one. And if you need another column, you need to be explicit, so you can list non-aggregate columns/fields in a GROUP BY clause.

                 

                2. Do not put fields as the calc dialog sees them (tableO::fieldName). SQL uses this notation:

                 

                    TableName.columnName

                 

                (And the CASE (upper/lower/mixed) of these names does not matter in SQL statements! But that 'dot' is used not '::'. )

                 

                You can skip the TableName in the SELECT clause if

                - the columns are uniquely named (one or more tables)

                - which means if a single table is named in the FROM clause, just list the columns/fields in the SELECT clause.

                 

                3. Use arguments/parameters to specify the table/field to be used and FM/eSQL will take care of proper quotation, etc. 

                 

                -- sent from my iPhone4 --

                Beverly Voth

                --

                • 5. Re: ExecuteSQL & date ranges
                  coherentkris

                  Wim's example was missing field and row delimiter parameters...

                   

                  ExecuteSQL(

                  "SELECT count (*) FROM Usage2 WHERE Usage_Date >= ? AND Usage_Date <= ?" ; "" ; "" ; decay3::g_date_start ; decay3::g_date_end

                  )

                  • 6. Re: ExecuteSQL & date ranges
                    coherentkris

                    Hi Bev.. In FMSQL COUNT(*) FROM table returns the number of records in table and COUNT (field) FROM table returns the number of non null values in the table::field...right? JUst checking to make sure I understand the functionality.

                    • 7. Re: ExecuteSQL & date ranges
                      taylorsharpe

                      One big advantage of COUNT ( * ) is that it is much faster than counting any specific field.  It returns the number of rows almost instantly compared to counting anything else.  I used to specify the primary key in my counts and when it was pointed out that this is much faster, I changed over to this for when I want to know how many records there are in a search. 

                      • 8. Re: ExecuteSQL & date ranges
                        beverly

                        Yes. How many fields do you need to count to get the number of records? Use your key field as:

                         

                        count(my_pk_field) from my_table...

                         

                        Your key field should always have a value.

                         

                        -- sent from my iPhone4 --

                        Beverly Voth

                        --

                        • 9. Re: ExecuteSQL & date ranges
                          beverly

                          Don't know who said 'much' faster. Then again other than FMI, I don't believe anyone knows under-the-hood eSQL...

                           

                          It depends on the database, table, # fields, etc. As I tested in FM, MS SQL, MySQL that (*) can be terribly wrong on so many levels that my practice is to list column(s) as needed. If you have a complex JOIN, columns named the same in multiple tables or using other aggregates then (*) even for COUNT is bad. Thus my saying NEVER to its usage.

                           

                          -- sent from my iPhone4 --

                          Beverly Voth

                          --

                          • 10. Re: ExecuteSQL & date ranges
                            DavidZakary

                            No joins at all (at the moment). This is being used to get the record count for a number of relatively basic queries for display on a dashboard.

                             

                            The main table is not very wide but currently contains just under 29 million records. The first query - to get just the basic found count is very quick.

                             

                            The second query, which was for getting the found count between two dates is taking a lot longer. Not really surprising given the 29 million records. Right now, it is faster to go to the layout, perform a traditional find, grab the found count and return to the dashboard. I'm hoping that will change as I want to get more into the habit of using ExecuteSQL in order to learn.

                             

                            There will likely be a couple of other queries to set up but I'm waiting on the client to see exactly what they want.

                             

                            Thanks for the help everyone.

                            • 11. Re: ExecuteSQL & date ranges
                              steve_ssh

                              Hello David,

                               

                              Regarding Performance:

                               

                              1)  I believe that in another thread on this forum, Wim has mentioned something about uncommitted records in the target table resulting in an ExecuteSQL performance penalty due to under-the-hood behavior where the server sends far more data to the client than would be otherwise be required.

                               

                              @Wim:  If you are still reading this thread, I hope you will please confirm/refute what I (vaguely) remember here -- I've been searching for the post where you may have mentioned this, but I have not yet found it.  Also, I can not remember whether this comment specifically related to the use of aggregate functions, or any type of SELECT statement.

                               

                               

                              2) If your dashboard functionality involves any Max calculations, there is an interesting read in a thread started by Tim Dietrich a little over 1/2 a year ago:

                               

                              https://fmdev.filemaker.com/message/128267

                               

                               

                               

                              HTH & Best,

                               

                              -steve

                              • 12. Re: ExecuteSQL & date ranges
                                wimdecorte

                                steve_ssh wrote:

                                 

                                Hello David,

                                 

                                Regarding Performance:

                                 

                                1)  I believe that in another thread on this forum, Wim has mentioned something about uncommitted records in the target table resulting in an ExecuteSQL performance penalty due to under-the-hood behavior where the server sends far more data to the client than would be otherwise be required.

                                 

                                @Wim:  If you are still reading this thread, I hope you will please confirm/refute what I (vaguely) remember here -- I've been searching for the post where you may have mentioned this, but I have not yet found it.  Also, I can not remember whether this comment specifically related to the use of aggregate functions, or any type of SELECT statement.

                                 

                                Steve: your recollection is correct.  If the target table has an open record in the client's session, FMS will send the client ALL the data for the ExecuteSQL() to be resolved.

                                It could be any record in the target table, not just the record that the user is asking for.  Open records by other users in their sessions do not matter and do not caue the slowdown to happen.

                                 

                                Did a demo of this yesterday to the Toronto developer's group.  The numbers from the demo with a 125,000 record table:

                                 

                                - no open record: 25 milliseconds for the first request, <1 ms for the next requests (data is cached)

                                - open record: 7,500 milliseconds for the first request, 6,500 ms for the next requests (data is cached)

                                 

                                The slowdown is linear with the # of records obviously; more records = more data that has to travel down the wire from FMS to the client.

                                • 13. Re: ExecuteSQL & date ranges
                                  steve_ssh

                                  Thank you Wim!

                                  • 14. Re: ExecuteSQL & date ranges
                                    JamesPeragine

                                    Very Interesting guys!   Wim what is the solution then in this situation?  Does the user just have to commit the record first or does the client have to close all records that are open with that table before Executing the SQL.  Is there a sleeker method than both of these?

                                    1 2 Previous Next