Acumatica DAC Extensions — Avoiding the Overfetch Trap

A performance ticket landed on my desk a while back: a Sales Order screen that had gotten progressively slower over two years of incremental customization. No single change was the culprit. The cause was a dozen PXCacheExtension classes, each individually reasonable, collectively pulling in far more data per row than the screen needed. This is the overfetch trap, and it is one of the easiest ways to quietly wreck an Acumatica screen's performance without a single obviously bad line of code.

Every extension field rides along on every query

When you add a field to a DAC via PXCacheExtension<T>, that field becomes part of the base table's row shape for every query against that DAC — not just the screen you built it for. A field you added for a specialized invoice-approval workflow gets selected on every AR Invoice load, every GI built over ARInvoice, every REST API call against the Invoices entity, whether or not the caller cares about it.

public sealed class ARInvoiceExt : PXCacheExtension<ARInvoice>
{
    public static bool IsActive() => true;

    // Fine on its own — one decimal column
    [PXDBDecimal(2)]
    [PXUIField(DisplayName = "Approval Threshold")]
    public decimal? UsrApprovalThreshold { get; set; }
    public abstract class usrApprovalThreshold : PX.Data.BQL.BqlDecimal.Field<usrApprovalThreshold> { }
}

One field like this costs nothing measurable. The trap is additive: ten customizations over a few years, each adding two or three fields to the same heavily-trafficked DAC, and suddenly every ARInvoice row carries forty extra columns that 95% of call sites never read. SQL Server does not care that you didn't need the column in your SELECT *-equivalent BQL projection — Acumatica's default select behavior for a DAC pulls the full declared row shape unless you explicitly project a subset.

The worse version: unbound fields computed on every RowSelected

The overfetch trap gets meaningfully worse when the added field is not a stored column but an unbound, calculated one populated in a RowSelected handler that runs a BQL select of its own:

protected virtual void _(Events.RowSelected<ARInvoice> e)
{
    if (e.Row == null) return;
    var ext = e.Row.GetExtension<ARInvoiceExt>();
    // Runs once PER ROW, every grid paint, every refresh
    var openCount = PXSelect<ARInvoice,
        Where<ARInvoice.customerID, Equal<Required<ARInvoice.customerID>>,
        And<ARInvoice.released, Equal<False>>>>
        .Select(Base, e.Row.CustomerID).Count;
    ext.UsrOpenInvoiceCount = openCount;
}

This is the pattern that turns a 50-row grid into fifty extra round trips on every screen paint. It is also the single most common root cause I find when a client says "the invoice screen used to be fast."

Practical fixes, roughly in order of effort

• Project only what the screen needs. If a grid or GI only displays five fields, select those five via a BQL projection or a dedicated view rather than relying on the DAC's full row shape — cheap wins on wide tables with many extensions.
• Cache expensive lookups. If RowSelected needs a value like "open invoice count," compute it once per graph instance in a dictionary keyed by customer ID rather than once per row per paint, and invalidate it deliberately rather than on every event.
• Move heavy computation out of RowSelected entirely. Field defaulting belongs in FieldDefaulting; persisted computed values belong in RowPersisting or a scheduled recalculation, not a live query on every UI refresh.
• Audit accumulated extensions periodically. On a mature instance, list every PXCacheExtension against your busiest DACs (ARInvoice, SOOrder, INTran, GLTran) and ask, honestly, whether each field is still used. Dead extension fields left from a decommissioned integration are pure overhead with zero benefit.
• Prefer unbound fields with no backing query when a value can be derived from fields already on the row. A calculated discount percentage from two existing decimal fields costs nothing extra; a calculated field requiring its own database round trip costs real money on every load.

How to actually find the overfetch, not guess at it

Turn on the Trace screen (or the built-in performance profiler on the affected screen) and look for two signatures: a SQL statement whose column list has grown suspiciously wide compared to what the grid displays, and a repeated near-identical query executed once per visible row. The first tells you which DAC extensions to audit; the second tells you exactly which RowSelected handler to fix. Guessing which customization is the culprit from memory, on an instance with a decade of accumulated changes, wastes far more time than five minutes with the profiler open.

Wrapping up

DAC extensions are cheap individually and expensive collectively, because every field you add rides along on every query against that table forever, and every RowSelected handler you write runs far more often than intuition suggests. Project narrowly, cache expensive lookups at the graph level, keep RowSelected free of anything that touches the database, and revisit old extensions with the same scrutiny you'd give new ones — the overfetch trap is almost always a decade of individually-reasonable decisions, not one bad commit.