PXCache vs PXSelect in Acumatica — Choosing the Right Tool

I still see this question in code review more than almost any other: should this line read from Base.Caches[typeof(ARInvoice)] or run a fresh PXSelect? They look interchangeable in a lot of tutorials, and in a narrow sense they even return the same rows sometimes. But they answer different questions, and picking the wrong one is how I've seen customizations silently show stale data, or worse, hammer the database inside a loop that should have been a single cache lookup.

PXCache is memory, PXSelect is a query

PXCache is the in-memory, per-graph-instance object holding rows the graph has already loaded or touched this session - every row it knows about, tagged Inserted/Updated/Deleted/Unchanged, available without touching SQL Server at all. PXSelect (and its BQL cousins) is a query definition that, when you call .Select(...), executes against the database - but with an important caveat: BQL selects are cache-aware too. A PXSelect against a DAC the graph already has cached rows for will return cache-tracked instances for any row already present, and only round-trip to SQL for rows it doesn't yet hold. So the two aren't strictly "memory vs database" - PXSelect is "database, filtered through the cache," while direct cache access is "memory, no query at all."

// Cache access - no SQL, reads whatever is already tracked this session
PXCache cache = Base.Caches[typeof(ARInvoice)];
var cachedRows = cache.Cached.Cast<ARInvoice>();

// PXSelect - a real query, cache-aware but will hit SQL for anything
// not already tracked
ARInvoice inv = PXSelect<ARInvoice,
    Where<ARInvoice.refNbr, Equal<Required<ARInvoice.refNbr>>>>
    .Select(Base, refNbr);

Use the cache when you need "what the user is looking at right now"

The cache is the right tool whenever you need the in-session, possibly-unsaved state of a row - a value the user just typed but hasn't saved yet, or a row that was inserted this session and has no primary key in the database at all. Base.Transactions.Current, Base.Transactions.Select().FirstTableItems, and Base.Transactions.Cache.GetStatus(row) are all cache reads. A BQL select at this point would either miss the unsaved row entirely (it doesn't exist in SQL yet) or return the persisted version instead of the version with the user's in-flight edit, which is the exact bug I've been called in to fix more than once - a validation handler that "worked in testing" because the test always saved first, then failed for real users editing a grid line that hadn't posted yet.

Use PXSelect when you need data outside this graph's current state

PXSelect is correct whenever the data you need isn't guaranteed to already be loaded into this graph's cache - a lookup against a different DAC entirely, an aggregate across rows the user hasn't scrolled to, or any query where the WHERE clause depends on something other than "rows already in memory." Trying to satisfy this from the cache means manually filtering cache.Cached, which only contains what's been loaded - if the graph hasn't selected those rows yet, they simply aren't there, and a cache-only approach will silently return an empty or partial result instead of the correct one.

protected virtual void _(Events.FieldUpdated<ARInvoice.customerID> e)
{
    if (e.Row == null) return;
    // The graph has no reason to already have Customer rows cached -
    // this must be a real query.
    Customer cust = PXSelect<Customer,
        Where<Customer.bAccountID, Equal<Required<Customer.bAccountID>>>>
        .Select(Base, e.Row.CustomerID);
    if (cust?.CreditHold == true)
        e.Cache.RaiseExceptionHandling<ARInvoice.customerID>(e.Row, e.Row.CustomerID,
            new PXSetPropertyException("Customer is on credit hold.", PXErrorLevel.Warning));
}

The trap that catches experienced developers: cache reads can be stale relative to the database

A subtlety that bites people who otherwise understand the distinction: if another process (a different graph instance, a scheduled task, a direct SQL update) changed a row after this graph's cache last loaded it, reading from this graph's cache returns the stale in-memory copy, not the current database state. If correctness genuinely requires "the database's current value, not what I last saw," you need an explicit fresh select - and in a few cases, PXSelect's own cache-awareness works against you here too, because it will still return the cache-tracked instance rather than re-querying. Forcing a true re-read means either clearing the row from the cache first or, for the rare cases that need it, dropping to PXDatabase directly, which always hits SQL and never touches the cache (a distinction that deserves its own detailed treatment).

The rule I actually use

• Need the current graph's in-session, possibly-unsaved state? Read the cache.
• Need data this graph hasn't necessarily loaded, from any DAC? Use PXSelect.
• Iterating rows you already know are loaded (a grid's current rows, for example)? Cache - don't re-query what you already have.
• Need a guaranteed-fresh read regardless of what any graph has cached? Neither - that's a PXDatabase-level concern.

Wrapping up

PXCache and PXSelect aren't competing ways to do the same thing - they answer "what does this graph currently believe" versus "what does a query against this criteria return," and BQL's cache-awareness blurs the line just enough to cause real bugs when you assume one always means the other. Reach for the cache for in-session state and iteration over rows you already hold, and reach for a real select the moment you need something outside what this graph instance has already loaded.