First, if at all possible, do *not* perform Find All Records or allow your users so to do. How is anyone supposed to work with 13K records. In most instances, "active" records, records within x days, or some other formula is a much better option for sanity and support.
So the first thing your server is doing is sending all fields of those records to the client. Once there the sort can occur. Any unstored calculations are only going to further slow things down as all of these calcs need to be performed. Depending on what field types you have, and how many are included in the sort, it's going to take a while, and that sort is occurring on the client, not the server. Once the sort is done (locally), it becomes much faster going forward as all of the data has been downloaded and sorted on the client.
One option that I find works well is to open to a layout that has only one field (very quick to download), Enter Find mode there, then switch to the table, and perform a find that limits the found set in some fashion. The perform the find so that only that short set of working records is found and then sorted.
Is the creation date indexed always? Keeping the fields you sort on indexed will help. Set this in the field storage settings.
13k records is not that many depending on how wide the table is.
On the server you might see some improvements optimizing the cache size and using SSD storage. However you have to remember the sort is done on the client once all 13k records are sent to the client from the server. The server is not sorting the records for you. How good is the connection to the server?
I have done finds on tables with 1M+ records of 50 or so fields each and returned sets of 50k records then sorted on various fields. This takes only a few seconds even on a hosted solution over WAN that has a only 2 core CPU.
You may want to consider doing your finds on the server and returning only the records you need. This works really well.
key field and sort fields are indexed. This file has related tables and 297 fields 28 of which are un-stored due to using related fields for counts etc. this is a service call database and when someone calls in a new svc call sometimes it is useful to look back at all records etc
i just started running into this find all/sort delay which takes over 1 minute but once the first find all sort is done - i can sort all records in several fields instantly, so i put script in startup to pay the 1 min penalty at the beginning but waiting a minute for startup every day is unacceptable.
i also tried to make file cache 512 mb (largest allowed) no help
Sort happens on the client and is not affected by indexing.
At first first open records are loaded onto the client cache then the cache is sorted if necessary.
Any sorts after the first sort are faster because it's only sorting the cache and the overhead for loading all records from the host is not present or minimal.
At least this is my understanding
coherentkris is correct. + 1
Indexing has ZERO impact on Sorting. Sorting data occurs (except with WebDirect) on the Clients... so All the data is pushed down through the network.
This material and other performance topics are covered in detail in our Pro 15 Video Training Course.
I have a database for service calls on server which has some 13000 records with some related tables. it runs on an exclusive mac mini with server 14 installed and nothing else. very few users - less than 5
I use a startup script to open the db and load all records
stopped reading here.
solution for now:
Since in several cases the user MUST see/sort ALL or THOUSANDS of records to accurately analyze different svc call problems etc I decided to add script that checks the found count and dialog the user warning them of the SORT delay giving the user the choice of waiting a minute or two for sort to complete or cancel.
still need a way to analyze what really is driving slow sort of many records - is it client machine performance, too many unstored fields, or something else.
user is happy to be forewarned, but i need more knowledge on FMP sort performance
thanks to everyone for your input - Tect, Kris, Big Tom, Richard and Siplus
Thanks for taking the time to reply
AFAIK; Sorting on related values take at least 1000 as long time as on non-related values.
Sort 2000 Invoice on related Customer Names takes 10 seconds.
Sorting on the Customer ID is almost instant.
Maybe this is what you're experiencing?
It has been this way as long as I can remember. I hoped it was solved in FM server 15, but no luck. I really don't understand how FM can be so bad on sorting?