Sorting related records

[Matt Malyschko]

I have a list layout that has ~600 records. This layout contains fields from both the current table, and a related table.

 

I want to perform a sort on any of these fields. What I am noticing is the first time any sort is done on that layout, it takes quite a considerable time. After that, every sort is done instantly.

 

Is this due to the related records? Is there a way this can be done without the delay?

 

I can make a look-up field to grab the data into the same table, but I don't really want to be duplicating the fields.

 

Is there another method to speed up this sort process?

 

Thanks in advance.
 

 

Edit: this is a hosted solution, when run locally it doesn't have the delay.

[Wim Decorte]

Why 600 records in the found set?  I would concentrate on showing a smaller set of data, no user can look at 600 records all at once so it does not make a lot of sense to make FM work to update and sort those records when the user is not looking at them.

[Mark Scott]

Hi MattyM,

 

+1 for Wim's suggestion.  

 

What you're seeing could be either, or both, of a couple different phenomena.  A question: when you say "first time any sort is done on that layout, it takes quite a considerable time. After that, every sort is done instantly," do you mean "first time ever" or "first time in a given session, or, possibly, first time after the found set changes"?

 

If the former, then that's probably the result of fields being set to "Index as needed," with the first sort attempt being slow as the index is built.

 

But you probably indirectly answered my question when you comment that "this is a hosted solution, when run locally it doesn't have the delay."  This reveals some things about how FM works in a hosted environment and is a result of caching.  Sorting is done on the client machine, so the client needs all record data for the current found set before it can sort.  That first sort in a session (or after a major change in the found set) is slow because the client is still receiving the record data from FMS, in chunks of (usually) 25 records at a time.  Subsequent sorts are faster, because the client has now cached the record data and field indexes locally.  Even after a modest change in found set, much of the data will still be locally cached, although less so, obviously, for a more major change in found set.  Which brings us back to . . . Wim's suggestion!

 

(But 600 records doesn't sound like a huge amount of data to have to move from server to client, dependent, of course, on how "wide" your tables are.  Although you didn't say, I'm guessing that your hosted environment is across the WAN/internet, rather than a local (LAN) network, correct?  Hosted is always slower than local (non-hosted), and WAN slower than LAN.  One more thing you can do, in addition to designing a UI in which the user is "encouraged" to limit their found set to something more manageable, is to consider going with narrow tables, by farming some infrequently used fields off to a separate 1:1 table (and not showing them on your current layout).  Not only does FMS need to send all 600 records to client before the latter can sort them, it sends all the fields (except container data, usually) for those 600 records, so the fewer fields, the better.  One last thing . . . if you can "hide" related fields in, say, a popover, or a slide/tab panel control, they won't be fetched until displayed, so this can speed things up considerably, as well.)

 

hth,

 

Mark