Problem: New Sale” layout loads very slowly (two seconds).
Setup: FMS (hosted by AWS) and FMP. Both are current versions.
My point-of-sale system includes a “sale” table with approximately 300 fields. The parent table (anchor) is connected to dozens of child tables, many of which I believe are not necessary for a “new sale.” I believe this design dates back 25 years or more, and is extremely inefficient.
Opening a “New Sale” layout takes approximately two seconds. Discounting an item opens a popup; closing the popup takes two seconds as the underlying layout repopulates.
This delay is not a dealbreaker, but it’s noticeably slow. I’m therefore investigating ways to speed this up. There are relatively few calculations, so I suspect the delay is due primarily to the poor design (300 fields), large number of unnecessary relationships, and latency.
I copied the layout and started deleting objects to see how much performance improved. Answer: none. Even a blank layout takes two seconds to open.
I’m considering creating a new table and layout with only the fields needed for a “new sale” (e.g., invoiceNumber, customerNumber, etc). All other updates would be done via scripting and/or PSOS.
300 fields is a lot to download when loading a record. I think your approach of a new table could be the most reasonable way to go forward.
Another way is to split data to separate tables. If you start refactoring the table one way to look at it is to see required fields in the main layout or a list. Rest of the fields can be split to separate table or multiple tables with 1-1 relationship.
Or then just start from scratch and create totally new table.
The issue is likely to be a calculation. Regardless of the # of fields, if it is taking 2 seconds when the pop-up closes, it will be a re-evaluation of a calculation, because you would have cached the record first time round. Calculations however will take time to re-evalaute depending on their nature. If it references a related field, then potentially longer because of the requirement it has to query server for the join result (itself a round trip whose speed is dependent on your latency to server).
Simplest thing is a process of elimination. First just try removing everything from the layout (drag it to the side). If the pop-up closing is fast along with layout loading, then start slowly moving components back to the layout until you notice a discernible difference in speed and that is your likely culprit.
You’ll probably discover it is only 1 or 2 things on the layout that are contributing.
In my experience all performance issues like this are directly tied to relationships being evaluated as a query to the server takes time (latency dependent). A single calculation referencing a related field will need to query that relationship. If your latency is 30ms, then the total is 60ms for that query.
to compound the issue further, portals that themselves reference a related field (from the portal context) are even worse, as the result of that query can be different per row. So a portal that displays 20 records for example, which itself has a related field in it, must perform 20 individual queries to server per row, 60ms x 20 = 1.2 seconds and all of a sudden you start to see the issue… It’s also why portal scrolling can often be a bit jaggy, the pause is the relationship query evaluating… more related fields from more relationships in a portal = even worse… you get the idea.
That’s surprising and seems like an important clue.
Does it still take 2 seconds if you change the layout’s base table to a different one? Is this an old layout that was built in Classic theme, and may have a lot of cruft?
It seems viewing/saving a record takes long, which is probably due to the amount of data in that record (it’s downloaded, then uploaded entirely).
My guess would be that one or more of the fields contain a lot of data (long text).
1 - diagnose this. Now you have a command line Developer tool that may help you doing that. For me the most efficient way remains creating a table layout and adding all fields to it with conditional formatting like Length ( self ) > 300 → red background. Then it becomes quite easy to see which fields contain a lot of data.
2 - question if you still need this field. I remember in the old days people (including myself) having fields with “exploded keys” to mach a “or” relationship. This kind of old (and now useless) techniques can result in very “heavy” records
3 - if you still need them, move these fields to another table with a 1 to 1 relationship, except if you need to display them on a list layout (in this case, the sluggishness of scroll with related records would be worse than the current situation)
Relationships that are more than two hops away will cause delays - especially in calculations. Even the second hop should be avoided if you can.
Avoid field indexing as much as possible to reduce file bloat (and data transfer). Turn auto-indexing off. Turn on indexing for the few fields that need it.
A way to connect tables directly to the parent is to use an X join between them. We use a calculation field ( the calculation is simply 1, as a number ) on both sides. In the portal we use filters. This is flexible and can be much quicker than dragging data across multiple joins.
As far as I know dragging an object to the side doesn’t prevent it from being rendered. Only deleting it makes sure it’s not rendered any more. But I’m not quite sure.
I keep forgetting whether objects are rendered in a register or on a slider even though they are not currently visible. But I think they are, right? Maybe someone can shed some light on this.
As far as I am aware, components on the right side are not evaluated, only components that are visible on screen get rendered/evaluated. Same applies for things in tabs that are in the visible part of screen, they only get evaluated when first viewed or referenced.
Having said that, scripts can access components off to the side, e.g. for inserting into a container, or querying a layout objects content and so on, the act of referencing them in a script forces them to evaluate at that moment.
Another thing to look for is record-level privileges on the table. If you have a calculated condition determine record access, then this calculation will need to be evaluated for each record. Calculation would take longer if it referenced anything related. Note too when you go to a layout, first 25 records are downloaded/cached (50 in table/list view) so it may be 25 record-level calculations are evaluating.