10 Replies Latest reply on Sep 27, 2016 9:13 PM by user19752

    Executing - Execute SQL

    RobertWard

      I need to sort a column in my report for ascending and descending order. Using FileMaker's standard sort gives a progress bar and client is tired of waiting for the sort to finish. I am looking to using ExecuteSQL to sort for hopefully a faster result. I have my execute sql statement of:

      ExecuteSQL ( "SELECT city FROM client ORDER BY city DESC"; ""; "" ) but I not certain how to execute this command to sort my city field as a script step.

       

      I have tried using the script stop of Execute SQL but it fails every single time with no feedback as to what could be wrong. I would appreciate any help about this because I don't understand this.

        • 1. Re: Executing - Execute SQL
          alecgregory

          If you're looking to sort records in a found set then you'll need to use Sort Records. The ExecuteSQL function won't help. And the Execute SQL script step is for external SQL sources and of no relevance here.

           

          The key to fast sorting is to make sure the field you are sorting by is indexed and in the same table as the Layout Table.

           

          If this is the case and you are still seeing slow sorting then it is likely you either have a very large record set or the user is accessing the data over a WAN with high latency.

           

          Try reducing the found set before the search. For example, encourage the user to find a subset of records before sorting.

          • 2. Re: Executing - Execute SQL
            RobertWard

            @ Alecgregory  Thank you for your reply. We are sorting on all records, around 2,500 over a WAN to our hosted server, so that the records can be summarized, using sub-summary parts. The field is in the same table we are sorting on and field is indexed. So, the ExecuteSQL function is not used for this purpose?

            • 3. Re: Executing - Execute SQL
              alecgregory

              Yes, that can take time. Are you also seeing a progress dialog for the summary fields? E.g. Summarizing [Field Name] or just the sort dialog?

               

              There are ways to speed reports up, but none are straightforward and they tend to involve fairly hefty structural changes to a system.

               

              Kevin Frank has some detailed articles on reporting using the Virtual List technique. If you are willing to do a bit of work then this would be a good place to start: https://filemakerhacks.com/2016/04/27/virtual-list-reporting-part-1/

              • 4. Re: Executing - Execute SQL
                philmodjunk

                ExecuteSQL is not an option. And your delay is not the sort, but the summarizing.

                 

                You'll need to explore ways to reduce the amount of summarizing needed or live with the delays.

                 

                One option that can sometimes be used it to create a summary table that is updated nightly as the basis for your summary report. Where your original report might have one or more sub summary parts computing subtotals, your summary table has one record for each sub summary part with the sub total computed and saved as a simple number field. The nightly update script loops through your data and computes (or recomputes) the summary totals needed for your report.

                 

                Of course this is not practical for all data sets, but can make a report very fast to display when it can be made to work for you.

                • 5. Re: Executing - Execute SQL
                  RobertWard

                  @ Alecgregory.  I was always under the perception that ExecuteSQL function was manipulating my display of information. That rocks my world a bit. Now I get that putting that information into a variable/global list view makes more sense to me now. Thank you for you help.

                  • 6. Re: Executing - Execute SQL
                    user19752

                    Index don't help speed of sorting.

                    Tested on about 500,000 records hosted file,

                    first sort on indexed text field took about 100 sec.

                    second sort on unindexed text field took about 5 sec.

                    third resort on the same field on first sort, about 5 sec.

                    • 7. Re: Executing - Execute SQL
                      alecgregory

                      It's my understanding that indexing can help the speed of sorting for some data distributions because the indexes are stored in sorted order. FileMaker certainly allude to this, and they do mention that operations run right after indexing may be slow: Performance Optimization of FileMaker Databases | FileMaker

                       

                      ISSUE:
                      How to optimize FileMaker Databases.

                      RESOLUTION:

                      1. Index all fields that will be used for searching and sorting.  The indexing may cause slowness for the initial search, however once indexed, the information will be found faster the second time.
                      • 8. Re: Executing - Execute SQL
                        fmpdude

                        Visual FoxPro, a now defunct database product actually saved indexes in a file where you could select the index you wanted for any purpose.

                         

                        So, in code you could do something like this:


                        SET INDEX TO CUSTOMER

                         

                        And your records would be in customer order (however you defined that). Thus, the sort was nearly instantaneous. Of course, you could have an index on multiple fields too.

                         

                        I'm constantly disappointed that in FMP, sorting is a incredibly slow operation (every time) with no index files like VFP had. Now, if you have small amounts of data, which may be FMI's expectation, then sorting is fine. But, for production data with large data sets, you probably need to resort to other techniques as Phil suggested.

                         

                        Now that we have "UNDO" in scripts (a version 1.0 feature, hurray!), stored, programmatically-select-able index files would be s welcome improvement.

                         

                        HOPE THIS HELPS.

                        • 9. Re: Executing - Execute SQL
                          RobertWard

                          Thank you everyone for your feedback and comments. They are all very helpful.

                          • 10. Re: Executing - Execute SQL
                            user19752

                            I think this mean optimize sorting you need smaller fond set, and optimize finding you need index.

                             

                            My test did on already indexed field. The reason 3rd sort done quicker than 1st is all data are in local cache.

                            1 of 1 people found this helpful