by sean » Mon Jun 12, 2017 4:19 pm
Hi Robrecht,
Yes this is a very common observation in Servoy.
Keep in mind that the reason that Servoy loads related records/foundsets in separate queries is to support a user really browsing through data.
Rather than query just what might be shown on a form, the complete record is loaded when it is first needed. But those records will also remain cached. Moreover, they are also updated via data broadcasting. Therefore, when you do something like navigate to detail of a related record, it is already loaded. It is even updated when another client session changes the records. This works nicely for many setups.
However, performance can become an issue on grid views which are showing many related records (or calculations).
It is best to profile your grid loading with the [url=localhost:8080/servoy-admin/database-performance]performance data[/url]
Clear the stats just before your form shows (for the first time) and then you really see both the volume of queries and the performance of each query.
Although, many queries are issued, the types of queries that load related records should be very fast. Be sure that tables are indexed properly (on foreign keys for example). The difference between 2 ms and 10 ms may not sound like much, but if the volume of queries is very high, then you see performance degrade quickly.
Also, grids have a higher impact in rendering too. Which client do you use? If NG client, then consider the Table Component (over the richer, but slower Table View form).
Finally, if your grid loading is optimized as best as it can be, but you still see a performance issue, then you can turn to in-mem datasource as you suggest. In this case, keep in mind that foundsets for in-mem datasource will not be updated via broadcasting. So you should be careful to ensure that you are not showing a "stale cache". Trap onDataBroadcast event to know about changes and force reload of a cached record where applicable.
We are actively researching into better support for in-mem data sources, and reducing the complexity at design-time for pagination and sorting as well as a way to receive data broadcasting automatically (i.e. based on known relations used in a query or something)
Software Engineer
Servoy USA