Horizon Energy: a 47-second dashboard under a 2 GB heap

Dominant cost. The loop performs one retrieve over the Asset association per work order: 60,000 iterations means 60,000 queries (the classic N+1), each with a round trip and its own query plan. On top of that, 60,000 persistent objects are held in a 2 GB heap while the loop runs, and the second retrieve loads the Asset set again. The counters themselves cost almost nothing; the retrieves and the memory do.

Replacement options, in order of preference:

1. Compute in the database. An OQL query grouping by asset and status (SELECT Asset/ID, COUNT(*) FROM WorkOrder WHERE Status <> 'Closed' GROUP BY Asset/ID) returns a few thousand rows instead of 60,000 objects, executed through a Java action using the OQL API. This removes both the N+1 and the heap pressure.
2. Materialise a stats entity. A scheduled event refreshes AssetStats (per asset, per status, count, last update). The dashboard then reads a small persisted set with an index on Asset; it is stale by the refresh interval, which is usually acceptable for a dashboard and is an explicit trade-off to agree with the business.
3. If you must stay in a microflow: retrieve WorkOrder once with the association, retrieve the Asset set once, and use list operations and Aggregate list - but note that Aggregate list still requires the list in memory, so this only removes the N+1, not the 60,000-object heap cost.

Do not keep a counter attribute on Asset updated inside the loop; it turns a read-heavy dashboard into a write-heavy job and will contend badly when several users open the dashboard at once.

WorkOrder.Status. The dashboard filters Status != 'Closed', which selects about 16% of the table. A plain index on a low-selectivity column with a != predicate often will not be used - SQL Server may prefer a scan. Better: index a composite that matches the actual access pattern, e.g. (Status, CreatedDate), so the sort can be satisfied by the index when the status is selective, or use a filtered index that excludes closed rows if your status is stored in a way that supports it.

WorkOrder.CreatedDate. Needed because the grid sorts on it. Without an index, every page load sorts the filtered set; with one, SQL Server can walk the index in order and stop after the page size. If the grid filters by status and sorts by date, the composite (Status, CreatedDate) serves both.

Association WorkOrder → Asset. Index the foreign key. Without it, every 'retrieve over association' and every $Asset/WorkOrder path is a scan; this is also the index that makes the second retrieve and any per-asset drill-down viable.

Description contains search. A leading-wildcard search cannot use a B-tree index - the index is ordered by prefix, and %text% has no usable prefix. Options: switch the default search to starts-with, which can use an index; add a SQL Server full-text index on Description and query it via a Java action or OQL (CONTAINS/FREETEXT); or restrict free-text search to a coded, indexed field such as an asset tag. Do not add an index on Description and expect the contains to speed up.

Cost discipline: every index slows inserts and updates on WorkOrder, which matters if work orders are created continuously. Define indexes in the domain model so they survive redeployment, and record the reasoning next to each one.

Instrument before you tune. Three layers, measured separately:

1. Database. Run the equivalent query by hand in SQL Server with the actual parameters and capture execution time and the plan (Query Store or an actual execution plan). If a scan over 380,000 rows is in the plan, no amount of runtime tuning will help. Capture page reads and CPU, not just elapsed time.
2. Runtime. Raise the relevant log node to TRACE (the connection-bus/query logging in the Mendix runtime logs) for a single reproduction, and compare the sum of query durations against the total microflow duration. The gap is runtime overhead - object instantiation, security XPath evaluation, list operations, garbage collection. Correlate with GC logs: 60,000 persistent objects plus a second Asset set in a 2 GB heap will show long collection pauses, and those pauses explain why the dashboard degrades in the afternoon when several users run it concurrently.
3. Client. In the browser network panel, compare time-to-response with time-to-render. A large data grid with no paging renders slowly after the response arrives, and users will attribute all of it to 'the dashboard'.

Report a baseline table: step name, wall-clock duration, database time, objects retrieved, rows returned, page size. Repeat after each change, one change at a time.

On the heap request: adding heap is reasonable only if GC logs show the runtime is collection-bound rather than the database being slow. If the time is 60,000 queries, more heap buys you nothing but a longer GC pause. The durable fixes are the ones that reduce objects per request - database-side aggregation, indexes and server-side paging with a modest page size (for example 20 rows) - and those also make the afternoon degradation disappear instead of postponing it.