Initial situation

Sometimes, the performance of larger database solutions can be observed to deteriorate in proportion to the number of users working in them simultaneously. These databases often use dashboards containing view elements and formula fields that select and display or process records from large tables, e.g., tasks, orders, leads, etc.

The cause

Formula fields and view elements dynamically update their content according to “long polling,” which means that every 30 seconds, these fields check whether their data is still up to date.

However, this rhythm is interrupted as soon as a user device learns through the synchronization processes that another user has made a change to some data relevant for formula execution. This behavior primarily affects “select” executions, as the system must know at all times whether the records displayed to the user are the ones they should see based on the corresponding conditions.

This results in the following behavior:

1. Users 1 and 2 are working simultaneously and both view the same dashboard with a view element that displays their tasks. The task table may contain several thousand records, but users only see the records that are currently relevant to them (e.g., a dozen each).
2. If neither of them edits any data field contents, the views update automatically every 30 seconds by querying the server for the current data status.
3. User 1 now opens one of their task records as a pop-up above the dashboard and changes a data value, e.g., the status of the task.
4. User 1's device sends the information about the change to the server.
5. The server distributes this change to the current users of the database, in this case User 2.
6. User 2's device notices the change and—outside of the “long polling” cycle—retrieves the data to be selected from the server again, as this may change the data to be displayed for User 2.

The superlative

But now imagine that instead of two users, ten/twenty/thirty are working simultaneously. Every time any user makes a change, the devices of all the other nine/nineteen/twenty-nine users would then request the data from the server again, which slows down the server and thus also the working speed of all users.

The solution: “Delayed Select”

Attached to this article is a database that demonstrates the principle of “delayed select” in practice. The “select” statements are not located in the views and formula fields; instead, ‘select’ is executed once via a tab trigger when the dashboard is displayed (or via the “Refresh” button if necessary) and the result is temporarily stored in a client-side text field. The content of this text field is then decrypted again by formula fields and views in order to collect the records to be displayed using the record() function in a "for" loop.

On the surface, this makes virtually no difference (except for the need to refresh the results by reopening the dashboard or pressing the button), but in the background, it avoids both long polling and forced refreshes every time the data changes. Applying this principle has already been proven to make a significant difference in usability in several cases and to ensure stable performance even with a large number of simultaneous users.