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.
@ 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?
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/
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.
@ 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.
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.
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
How to optimize FileMaker Databases.
- 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.
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.
Thank you everyone for your feedback and comments. They are all very helpful.
1 of 1 people found this helpful
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.