Hello,
I was going through the documentation, and found this best practice (https://success.outsystems.com/documentation/11/building_apps/data_management/data_operations/best_practices_for_fetching_data/): "Iterate only once and avoid using indexers ([ ]) over a query". The Description for this was: "Iterating more than once over a query result isn't a good practice. By doing so, the query results are copied into the memory. The same applies when using direct indexers (like query[2].value expressions)."
I also referred to this discussion: https://www.outsystems.com/forums/discussion/91712/best-practices-queries/According to the answer there, the platform uses a database cursor to fetch the records one-by-one to conserve memory, and since the cursor cannot be reset, this best practice is recommended.
Is the same true in case of aggregates fetched in blocks or refreshed in client actions? For example, if I have an aggregate in a block, is it a bad practice to use a For Each over the aggregate or to use list indexes for aggregates (like query[2].value) in client actions or expressions?
According to the documentation: "If conserving server memory is necessary, try to avoid doing multiple iterations (for each, table/list records) over the same query." As can be seen here, it explicitly mentions the server memory, and not the client memory.
However, the documentation also mentions: "This also applies to lists displayed on a screen. For example if you iterate a query once and then display it in a table records, your query is copied into memory. This happens because the rendering of the table records iterates through the query results again." This seems to suggest that the cursor is used even for the client-side aggregates.
So, here are my questions, summarized:
Thanks and Regards, Tushar Godbole
Hi Tushar,
Short answer: the “iterate once / avoid indexers” advice is about server memory in O11. It doesn’t apply to aggregates you consume on the client.
1. Cursors only exist on the server. A screen or block aggregate in Reactive/Mobile is not executed by the browser. The platform runs it on the server as part of the screen’s data fetch, reads all the rows, serializes them to JSON and sends them to the client. The client never holds a cursor, and it never accesses the database directly.
2. On the client, the result is a plain list already in memory. A For Each over it, an expression like GetOrders.List[2].Order.Name, or a List widget rendering it does not re-query or copy anything. Multiple iterations and indexers are fine client-side. Mobile local storage aggregates work the same way: the SQLite result is materialized into a list before you use it.
3. So the best practice protects server memory only. When you run an aggregate or SQL node inside a server action, the result is first read through a forward-only data reader. One iteration streams the rows. A second iteration or an indexer forces the platform to load the whole result into memory first. For 50 rows this is irrelevant; for 100k rows in a timer it matters.
4. A cursor is not one database round-trip per record. The data reader receives rows in network packets, so many rows arrive per round-trip. What actually matters is how many rows you request:
On the client side the equivalent concern is payload size, because the whole result travels over the network. Fetch only the attributes and rows the screen needs.
Hi Ahmed,
Thank you very much for your detailed reply and clearing my doubts.
I was particularly confused about the part regarding the list rendering mentioned in the documentation, because it seemed to suggest that the client-side might be involved in moving the cursor ahead, but that is not the case after all, and the best practice is only for conserving server memory.
Thanks and Regards,Tushar Godbole