Diagnosing an N+1 List Screen
A Reactive Web back-office screen lists invoices for the finance team. It uses List Navigation with 50 rows per page over 4,800 invoices. Each row shows invoice number, customer name, and the date of the last payment. The list Block contains its own aggregate that fetches the customer, and a nested LastPayment Block runs a second aggregate per row. The screen takes 6 to 9 seconds. Service Center monitoring shows the top aggregate at 5.8 seconds, and a single page view produces about 101 aggregate executions. The database has no index on Invoice.CustomerId or Payment.InvoiceId. Users say filtering by customer makes it worse, and the total-records counter shows zero on filtered pages. The team proposes raising Max Records to 500 to reduce round trips.
1. Explain the cause of the 101 executions and why raising Max Records will not help. What evidence would you collect?
Read the worked answer
The count is the signature of an N+1 pattern. One aggregate loads the 50 invoices, then each row's Blocks execute their own aggregates: 50 customer fetches plus 50 last-payment fetches, plus the list aggregate, equals 101. The nested Blocks are not batching anything; each instance runs its aggregate independently during rendering.
Raising Max Records to 500 makes it worse. It multiplies per-row work to roughly 1,001 executions, renders 10 times more DOM, and increases payload and client render time. It reduces the number of page navigations, which is not the bottleneck.
Evidence to collect: Service Center monitoring for screen and aggregate durations, execution counts per screen, and the slowest SQL statements. Add temporary Log Message calls with timestamps around the list aggregate and one row block to confirm serialization. Inspect the aggregate definitions for fetched attributes, joins, and filters, and check the actual query plan for the missing indexes. In the browser, record payload size and time to first meaningful render to separate server time from rendering time.
What would disprove the diagnosis: if the 101 executions were mostly cache hits, durations would be low; they are not, and the 5.8-second top aggregate indicates real database work.
2. Redesign the data retrieval for the list, including pagination and total count, and state the trade-offs between aggregates and SQL.
Read the worked answer
Target: two queries per page. One aggregate returns the 50 invoices joined to Customer for the name, with a grouped last-payment date. The cleanest aggregate form is Invoice joined to Customer, plus a grouped aggregate over Payment (Group By InvoiceId, Max Date) joined back, or a single advanced SQL query with a derived table. The second query is a Count aggregate with the same filters as the list, which also fixes the zero counter on filtered pages.
Pagination: keep List Navigation with StartIndex and Max Records = 50, and apply the customer filter in the aggregate, never after fetching. Do not fetch all 4,800 rows into a client variable. If the filter is optional, build it with a parameter and a conditional filter, not string concatenation.
Trade-offs: aggregates are declarative, stay aligned with the entity model, and are easier for the team to maintain, but complex grouping with multiple joins can become unreadable and may fetch more columns than needed. Advanced SQL gives control over the plan and allows window functions, but it bypasses the entity layer, so validation, tenant filtering, and permissions must be enforced explicitly and the query must use parameters.
Do not solve this with a client-side join or a For Each loop calling a server action; both recreate the N+1 in a different place.
3. How do indexes and only-fetch-used-attributes change the outcome, and what would you measure after the change?
Read the worked answer
Indexes change the shape of the work. Invoice.CustomerId supports the join and the customer filter; Payment.InvoiceId supports the grouped last-payment lookup. Without them, each of the 50 rows can drive a scan, which is why the per-row design degraded so badly. Add them as entity indexes so they are part of the deployment, and verify with the query plan rather than assuming.
Only-fetch-used-attributes reduces payload, not query count. Fetching 12 columns when the screen shows 5 wastes network and memory and can prevent covering-index behaviour, but it will not turn 101 executions into 2. Treat it as a second-order improvement after the N+1 fix.
Measure after the change: aggregate executions per page view (target 2), aggregate duration, screen render time, response payload size, and database reads/CPU for the list query. Test with no filter, with a customer filter that returns few rows, and on the last page. Compare p50 and p95, not averages, and watch the count query separately because it can become the new bottleneck on a large table.