Vendor Record: A Slow Grid and an Overnight Sync That Overruns
A Vendor record type is backed by a database view joining VENDOR, INVOICE and REGION. It holds 1.4M vendors and 9M invoices. Nightly data sync takes 2h40m. The 'Vendors by Region' grid pages 25 rows at a time but takes about 11 seconds to paint, and it includes a column 'Open Invoice Count' computed inside a!forEach with a!queryRecordType against the Invoice record type for each row. APAC users report the sync window (00:00-03:00 server time) overlaps their morning work. VENDOR.LAST_MODIFIED is maintained reliably by the ERP on every insert and update; INVOICE inserts do not touch the parent VENDOR row. Users need the count column and also want to filter the grid to vendors with count > 0.
1. Why is the grid slow, and what are two viable ways to keep the 'Open Invoice Count' column? State the trade-offs, including what each fix means for the 'count > 0' filter.
Read the worked answer
Two problems compound:
- N+1 querying in the UI layer. 25 rows x one record query each = 25 round trips per page load, and grid cells are re-evaluated whenever the grid refreshes. The cost scales with page size, not with what the user sees.
- Each count query aggregates a 9M-row INVOICE table with no supporting index on
vendorId, so every one of those 25 calls does real work.
Fix A - precompute in the data source. Add OPEN_INVOICE_COUNT to the view (or a summary table refreshed off invoice events) and expose it as a record field. The grid becomes a single query. Trade-off: freshness is only as good as the refresh, but the column is now a real field, so count > 0 can be filtered in the query itself and paging stays correct.
Fix B - batch in the interface. Query the page of vendors, then run one aggregate query with vendorId IN (25 ids) grouped by vendor, build a map in a!localVariables, and render index(map, ri!vendorId, 0). Two queries instead of 26, and always fresh. Trade-off: it recomputes on every page change, and the filter is the real trap - a!gridField filtering applies only to fetched rows, so filtering on a computed column would only filter the current page. Fix B therefore forces the filter into the data source anyway, which usually collapses back into Fix A.
2. The sync window is hurting APAC users. Recommend a sync configuration and justify it using the facts in the scenario, including what can go stale.
Read the worked answer
The scenario hands you the key fact: VENDOR.LAST_MODIFIED is reliably maintained. That makes it possible to stop re-reading 1.4M rows every night and pick up only changed rows, using the last-modified column as the sync watermark. That is the first lever, because the 2h40m is dominated by volume, not by change rate.
But do not stop there - two dependencies break:
- The count is invoice-driven. Invoice inserts do not update VENDOR, so an incremental vendor sync will never refresh
OPEN_INVOICE_COUNT. Either sync invoices as their own record type (with their own last-modified) and compute at query time, or refresh the summary table from invoice events. - Deletes are invisible to an incremental sync. A row deleted at the source simply stops appearing in the changed set. Keep a periodic full sync (weekly, off-peak) as a backstop, or have the source set a soft-delete flag the sync can read.
So the recommendation is: incremental nightly sync for VENDOR, invoice-driven refresh for the count, and a weekly full sync off-peak. Do not simply move the full 2h40m window later - it will drift back into someone's working hours as data grows.
3. A stakeholder asks you to add a grid filter for Open Invoice Count > 0. Where must that filter live, and why?
Read the worked answer
It must live in the data source query (view, summary table, or the filters passed to a!queryRecordType), not in the grid's filter configuration.
Reasoning: with server-side paging, the grid receives 25 rows, not the 1.4M-row population. A grid-level filter operates on the rows it has, so a user would see 'count > 0' applied to page 1 only, and vendors with open invoices sitting on page 60 would never appear. The result looks like missing data rather than a filtering bug, which is worse.
If the count is a computed column rendered in the interface (Fix B in the previous question), you cannot push the filter down at all without materializing it somewhere. That is often the deciding argument for precomputing the count in the database: it makes the column filterable, pageable and sortable, which is exactly what the stakeholder asked for.