The Aggregation That Doubled the Screen Load
A team ships a Traditional Web app listing 40 active projects on a dashboard. Each row shows the project's client name and the count of open tasks. The screen uses a single Aggregate that joins Project to Client and to Task, with a filter on Task status. Load time is 1.8s in test but 9s in production, where one client has 30,000 tasks. The developer proposes switching the whole screen to a SQL query. Meanwhile a reviewer notes the Aggregate returns 41 rows, not 40, and one project appears twice. The team has one week before release and no DBA available to tune indexes.
1. Diagnose why the row count is 41 and why production is slower than test, using only OutSystems aggregate semantics.
Read the worked answer
The extra row is the classic symptom of joining two *-to-one/one-to-many relationships in one Aggregate: joining Project to Task at row level multiplies rows per task, and if a project has tasks with different statuses, the filter leaves partial rows. When a project has zero matching tasks, an inner join drops it entirely; if the developer set the join to an outer join to keep projects, the same project can surface through multiple joined rows. The fix is to split concerns: one Aggregate for Project+Client (one row per project), and a separate Aggregate for Task counts grouped by ProjectId with a Count attribute, then merge in the screen or via a structure. Production slowness is the fan-out: 30,000 task rows are materialised and filtered in memory before grouping, so the cost scales with the largest client, not with 40 projects. Test data hides this because no single client has enough rows to expose the multiplication.
2. The developer wants to replace everything with raw SQL. Argue for or against, and state what you would actually change first.
Read the worked answer
Against, as a first move. Raw SQL bypasses the platform's entity validation, caching hints, and the automatic index-awareness OutSystems applies; it also makes the screen harder to maintain and to secure (SQL injection risk if parameters are concatenated). The genuine problem is the join shape, not the Aggregate engine. Change order: (1) remove Task from the main Aggregate; (2) create a grouped Aggregate on Task filtered to open status, grouped by ProjectId, exposing Count; (3) aggregate the two results in a screen action into a List of a local Structure; (4) only if the grouped Task Aggregate is still slow on 30,000 rows, add an index on (ProjectId, Status) - which is a DBA-free schema change in Service Studio - and re-measure. Escalate to SQL only if measurements still fail after that.
3. How would you verify the fix before release without production data?
Read the worked answer
Seed the test environment with a deliberately skewed dataset: 40 projects, one client with 30,000 tasks, several projects with zero tasks, and at least one project whose tasks span multiple statuses. Then assert three things: the list length is exactly 40, no project id appears twice, and the open-task count for the heavy client matches a hand-computed value. Measure the screen's server-side execution time in Service Center, not just browser rendering, and compare against the 1.8s baseline. Skewed data is the point - uniform test data will always pass and proves nothing about fan-out.