Acumatica Performance — Diagnosing a Slow GI

A Generic Inquiry that took two seconds at UAT and takes forty seconds six months into production is the single most common performance ticket I get on established Acumatica instances, and it is almost never a GI designer bug — it is data growth exposing a query shape that was never going to scale, hiding behind a UI that made building it trivially easy.

Step one: get the actual SQL, not a guess

Every GI compiles down to a BQL query and ultimately a T-SQL statement. Guessing which join or condition is expensive wastes time; get the real query from SQL Server's Query Store or from a Profiler trace while running the GI, and work from that, not from reading the GI designer and imagining what it probably generates.

SELECT TOP 20
    qt.query_sql_text,
    rs.avg_duration/1000.0 AS avg_duration_ms,
    rs.count_executions,
    rs.last_execution_time
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
WHERE qt.query_sql_text LIKE '%ARInvoice%'  -- narrow to the GI's tables
ORDER BY rs.avg_duration DESC;

The four things I check, in order

• Unindexed join or filter columns. A GI joining on a custom field or filtering on something other than the table's natural key hits a scan instead of a seek. Check the execution plan for a "Clustered Index Scan" or "Table Scan" on a large table — that is the single highest-value thing to find.
• Client-side filtering hiding as a GI condition. Conditions built against calculated fields, or against fields exposed only after a join that could not be pushed into the join's ON clause, sometimes get evaluated after the row set is materialized rather than in the WHERE. The GI feels like it has a filter; the database does not agree.
• Fan-out from one-to-many joins. A GI joining an invoice header to its lines, then to its tax details, multiplies rows at each join. If the designer's Results Grid is set to show header-level fields against that multiplied row set, you are fetching far more rows than the user ever sees after grouping/dedup in the UI.
• Missing filter defaults on a large table. A GI over GLTran or ARTran with no default date range returns and sorts the entire history table on first load. Add a sensible default filter (a relative date range, "current period," etc.) so the unfiltered worst case never actually runs.

Fixing each cause

Missing index: add one covering the join/filter columns, respecting the existing convention of leading with CompanyID for multi-tenant tables — coordinate with SQL Server indexing practices covered in the indexing post in this series, and script custom indexes since upgrades can silently drop unmanaged ones. Fan-out: restructure the GI to aggregate the many-side before joining (a subquery relation or a supporting projection), or split into a master GI and a detail GI rather than one flattened result. Missing default filter: add one on the Parameters tab with a sane default value, not just an optional blank parameter — optional-but-blank still lets a user run the worst case by accident.

Wrapping up

Diagnose a slow GI from the actual SQL Server execution plan, not from staring at the GI designer, and always test against production-scale data before trusting the result. Index the real join/filter columns, catch join fan-out before it inflates the row count, and default every date-range parameter on large tables so nobody accidentally triggers a full-table sort.