What you're seeing is the delay caused by record caching. Normally, FileMaker will load only the first 25 records in a portal to the local cache. However, when you sort, it's required to load them all - the entire related set - because there's no way to sort unless it does. Depending on the size of the related records (number of fields, amount of data in each field), that caching can take some time over a slow connection (like the open WAN of the Internet).
You can speed it up some by restricting the amount of data on the related records - eliminate fields, use a one-to-one relationship to infrequently used, large fields.
Another issue is grandchild tables. Are you sorting on something that is more than one table away? That's an unstored operation, and will have to be repeated each time. Similarly, sorting on an unstored calculation will kill you, too.
Just some thoughts. Don't know which of these applies.
Your best bet is to use an auto-enter calculation to store the value in the child table. This will allow FileMalert to index it, and will dramatically speed up the operation. Yes, it means breaking relational storage rules, but sometimes you have to do that for practical reasons (like not being strung up by customers tired of waiting on your solution).
I've added a text field to the line items table called "Surname". As part of the script to add to the portal I have added a set field step which sets Surname with the Surname from the Contacts popover...
Then sort by Surname.
Does the job.. is that what you mean? / best option?
Well, that's not exactly what I meant, but it will work.
Don't forget that you could just display the related surname field and drop the item based surname field altogether.
And include that field (parent surname field) in the sort order.
There is a bit of complication in setting up a portal sort order this way because first you have briefly reselect the TO for the portal to be the parent record; then set the sort order; then set the TO for the portal to be the original child record; and add any other child-field sorts
That approach, however, will necessarily impose a performance penalty, as all the related records on the grandchild side will also have to be loaded.
Still better than the unstored calculation, but not as fast as duplicating the data into the immediate child table. You'll have to determine which approach strikes the right balance between performance and other overhead.
I really don't see how you figure that. Grandchild records loaded? Why?
If you're sorting on a field in that table, they have to. Can't just load the one field; have to load the entire record in the grandchild table.
I had this experience with a WAN solution about 18 months ago. Tried to be "well normalized" and use the grandchild records for sorting. Very slow, but only as they were caching. Added the data to the child table, and things sped up a lot.
That was confirmed by Vince Mennano when I asked the question at DevCon during the performance panel in 2014.