10 Ways to Speed Up Your OutSystems Aggregates

An Aggregate with no Max Records limit executes `SELECT * FROM Employee` with no row limit — on a table with 500,000 rows, this fetches all 500,000 rows on every screen load. Fix: In every Aggregate, click the Aggregate node (the top node in the Aggregate editor, showing the Aggregate name). In the Properties panel → Max Records, enter the maximum number of rows your screen will ever display. For list screens: Max Records = PageSize (typically 10-50) For dropdown sources: Max Records = 200-500 (max reasonable list) For full data export: Max Records = blank intentionally, but only in a background process, never on a screen TrueChange warning: 'Aggregate does not have a Max Records limit' — this is a warning, not an error, but should be treated as a required fix for production screens.

1/* Aggregate properties — set in Properties panel */
2Max Records:  20          /* for paginated list */
3Start Index:  StartIndex  /* Integer variable, default 0, for pagination */
4
5/* Pagination Client Action: go to next page */
6/* Assign: StartIndex = StartIndex + 20
7   Refresh Data: GetEmployees */
8
9/* Pagination Client Action: go to previous page */
10/* Assign: StartIndex = If(StartIndex - 20 < 0, 0, StartIndex - 20)
11   Refresh Data: GetEmployees */
12
13/* Count for 'Page X of Y' label */
14"Page " + IntegerToText((StartIndex / 20) + 1) + " of " + IntegerToText(Ceil(GetEmployees.Count / 20))

The N+1 query anti-pattern: for each employee row (N rows), executing one Aggregate to fetch the manager's name. N=1,000 employees = 1,001 queries. BAD pattern: For Each Employee in EmployeeList → GetManager (Aggregate: Employee where Id = CurrentEmployee.ManagerId) → Assign ManagerName = GetManager.List.Current.Employee.Name End For Each GOOD pattern: join Manager at the Aggregate level. In the main GetEmployees Aggregate, add the Employee entity a second time (self-join) as 'Manager'. Join Manager.Id = Employee.ManagerId using a With or Without join. Access Manager data as `GetEmployees.List.Current.Manager.Name`. This replaces N+1 queries with 1 single query containing a self-join.

1/* N+1 BAD — DO NOT DO THIS */
2/* For Each loop over 1000 employees */
3For Each (GetAllEmployees.List)
4  GetManagerSQL.DepartmentId = CurrentEmployee.DepartmentId   /* 1 query per iteration */
5  /* Result: 1001 SQL queries for 1000 employees */
6End
7
8/* N+1 GOOD — single Aggregate with self-join */
9/* Aggregate: GetEmployeesWithManagers */
10/* Sources:
11   Entity 1: Employee  (alias: Employee)
12   Join: With or Without
13   Entity 2: Employee  (alias: Manager, joined on Employee.ManagerId = Manager.Id)
14*/
15/* Access: */
16GetEmployeesWithManagers.List.Current.Employee.FirstName
17GetEmployeesWithManagers.List.Current.Manager.FirstName  /* manager name, one query */
18
19/* Also avoid N+1 with related entities via joins */
20/* Instead of fetching Department in a loop: */
21/* Add Department to the Employee Aggregate with a join once */

A filter like `Employee.DepartmentId = @DeptId` on a table with 100,000 rows does a full table scan without an index. OutSystems auto-creates an index for FK attributes (Entity Identifiers), but not for other filtered columns like IsActive, HireDate, or LastName. To add an index in O11: 1. Open Service Center → Factory → Modules → [your module] → Entities 2. Click the entity → Indexes tab → New Index 3. Name the index, select the attribute(s), check Unique if applicable → Create Index Common indexes to add: - IsActive (Boolean): for frequent `WHERE IsActive = True` filters - HireDate (Date): for date range filters - LastName (Text): for text search filters - StatusId: auto-created by FK, but verify it exists For compound queries filtering on multiple columns simultaneously (DepartmentId AND IsActive), create a composite index with both columns.

1/* Service Center: Entity index creation */
2/* Factory → Modules → YourModule → Entities → Employee → Indexes → New Index */
3
4/* Index 1: single-column on IsActive */
5Index Name:  Employee_IsActive
6Attributes:  [IsActive] ASC
7Unique:      No
8
9/* Index 2: composite for common filter (Dept + Active) */
10Index Name:  Employee_DeptActive
11Attributes:  [DepartmentId] ASC, [IsActive] ASC
12Unique:      No
13
14/* Index 3: for text search — limited value but helps starts-with */
15Index Name:  Employee_LastName
16Attributes:  [LastName] ASC
17Unique:      No
18
19/* ODC: same process via ODC Portal → Application → Entity → Indexes */
20/* ODC uses Aurora PostgreSQL — index syntax is managed by the platform */
21
22/* Verifying index usage: check SQL execution plan in Service Center */
23/* Look for: Index Seek (good) vs Table Scan / Index Scan (review) */

OutSystems automatically fetches only columns that are referenced in your app code. However, this optimization can be defeated by using `GetEmployees.List.Current.Employee.*` (all attributes via a structure variable) instead of specific attribute references. To verify which columns are being fetched: in the Aggregate editor, look at the column list. Columns shown in a darker weight are being used; lighter columns are not fetched. Grayed-out columns are excluded from the generated SQL. To help OutSystems minimize the SELECT: - Only add List/Table widget bindings for columns you display - Avoid binding the entire Employee record to a local variable if you only need Name and Email - For a name-only dropdown, only reference Employee.Id and Employee.Name in bindings This is most impactful for entities with Binary Data attributes (file content) — ensure binary columns are NOT fetched unless you are displaying file content.

Service Center is the O11 operations console. Access it at https://your-environment/ServiceCenter. To find slow queries: 1. Service Center → Monitoring → Database Logs (enable slow query logging first) 2. Or: Service Center → Monitoring → Request Logs → find a slow request → click to see its queries 3. Look for queries with high Duration. Click the query → 'Show Query Plan' Reading the plan: - Index Seek: optimal, using an index efficiently - Index Scan: reads all index entries — add a more specific filter - Table Scan / Clustered Index Scan: reading every row — missing index - Nested Loops with high estimated rows: possible N+1 pattern For ODC (Aurora PostgreSQL): 1. ODC Portal → Application → Logs → Database 2. Filter by Duration > 500ms to find slow queries 3. Copy the query and run EXPLAIN ANALYZE in psql or a PostgreSQL client TrueChange performance warnings (bottom pane): look for orange warnings about missing Max Records, large aggregates, or Binary Data in screens.

Tip 6 — Move expensive Aggregates to Server Actions: Screen Aggregates run in the browser rendering cycle. Aggregates in Server Actions run on the application server before sending the response. For complex multi-join aggregates, wrapping them in a Server Action and caching the result in a Session Variable can reduce per-request database load. Tip 7 — Use 'Only on demand' Fetch for below-the-fold content: Set the Aggregate's Fetch property to 'Only on demand' for data that is not visible on initial page load (tab panels, accordions, pagination). Trigger the fetch via DataAction.Fetch when the user scrolls to or opens the section. Tip 8 — Apply most restrictive filters first: OutSystems passes filters to SQL in the order they appear in the Filters tab. Place filters on indexed columns (DepartmentId, IsActive) before text-search filters (Index()). The database optimizer may reorder them, but explicit ordering helps with readability and some optimizer hints. Tip 9 — Use Aggregates with Count() instead of fetching a full list just to count: If you only need `GetEmployees.Count` (not the list), create a separate Aggregate with Max Records = 1 and the same filters — Count still returns the full count, but you don't transfer row data. Or use the Aggregate's Count property directly without rendering the List. Tip 10 — For reports on 1M+ rows, switch to Advanced SQL with streaming: Very large data exports should use Advanced SQL with server-side streaming rather than fetching all rows into memory. Consider delegating heavy analytical queries to a dedicated reporting solution rather than running them against the OLTP database.

1/* Tip 7: Fetch on demand pattern */
2/* Aggregate property: Fetch = Only on demand */
3/* Trigger in Client Action (e.g., OnTabSelected): */
4/* GetDepartmentDetails.Fetch  -- triggers the deferred aggregate */
5
6/* Tip 9: Count-only Aggregate for dashboard numbers */
7/* Aggregate: GetEmployeeCount */
8/* Sources: Employee */
9/* Filters: Employee.IsActive = True */
10/* Max Records: 1        -- limits row transfer, Count still accurate */
11/* Use: GetEmployeeCount.Count on screen, don't bind .List */
12
13/* Tip 6: Cache in Session Variable (use sparingly) */
14/* If the same list is needed on multiple screens and rarely changes: */
15/* Server Action: GetCachedProductList */
16/* Check Session.ProductListLoaded Boolean */
17/* If False: run Aggregate → Store in Session.ProductList → Set Boolean True */
18/* Return Session.ProductList */
19/* Invalidate by setting Boolean to False when products change */