PXDatabase.Insert vs Cache.Insert in Acumatica

Every so often I get asked to speed up a bulk data load - tens of thousands of rows, and the client's existing customization is doing it through the normal graph, one cache.Insert() and Base.Save() per record. It works, and it is also the slowest possible way to get those rows into the database, because every one of those inserts pays for the entire event pipeline: FieldDefaulting, FieldVerifying, FieldUpdated, RowInserting, RowInserted, RowPersisting, and every attribute's validation logic, per field, per row. PXDatabase.Insert is the escape hatch, and understanding exactly what it skips is the whole reason to reach for it deliberately instead of by accident.

Cache.Insert: the whole framework, and its cost

cache.Insert(row) puts a row into the cache with status Inserted and fires the complete event pipeline immediately - defaults get applied, attributes validate, your event handlers run. This is correct, and necessary, for anything a real user does through a screen, because the business rules encoded in those events are exactly what keeps the data consistent. The cost is real: dozens of small operations per field, per row, all in-process but not free, and none of it touches SQL until Base.Save() actually commits the transaction.

foreach (var csvRow in importRows) // 40,000 rows
{
    INTran tran = new INTran();
    tran = Base.Transactions.Insert(tran); // full event pipeline, every time
    tran.InventoryID = csvRow.InventoryID;
    tran.Qty = csvRow.Qty;
    Base.Transactions.Update(tran);        // FieldVerifying, FieldUpdated per field
}
Base.Save.Press(); // one transaction, but 40,000 rows of pipeline already paid for

PXDatabase.Insert: SQL, and nothing else

PXDatabase.Insert writes directly to the underlying table with a parameterized SQL statement. It does not touch any graph's cache, does not fire a single event, does not run attribute validation, does not apply PXDefault values, and does not participate in whatever graph instance called it beyond sharing its ambient transaction scope if one is open. It is, functionally, raw ADO.NET with Acumatica's connection and transaction handling wrapped around it.

foreach (var csvRow in importRows)
{
    PXDatabase.Insert<INTran>(
        new PXDataFieldAssign<INTran.inventoryID>(csvRow.InventoryID),
        new PXDataFieldAssign<INTran.qty>(csvRow.Qty),
        new PXDataFieldAssign<INTran.tranType>(csvRow.TranType),
        new PXDataFieldAssign<INTran.docDate>(csvRow.DocDate)
        // every column that matters must be assigned explicitly -
        // nothing defaults itself here
    );
}

Where PXDatabase.Insert earns its place

I use it in three situations, roughly in order of how often they come up: bulk data migration scripts run once during a go-live cutover, where I've already accepted responsibility for replicating whatever defaulting logic matters by hand; performance-critical scheduled processing jobs writing to simple log or staging tables that have no attached business logic at all; and, occasionally, deliberately bypassing an expensive or buggy event handler I don't control (a base-platform handler, or a third-party customization) when I've confirmed by reading its code that skipping it is safe for this specific write.

A middle ground that's often the wrong compromise

Some developers try to get "some" of the pipeline's benefit by manually calling specific attribute methods or replicating defaulting logic inline while still using PXDatabase.Insert for the write. In my experience this produces the worst of both worlds: all the maintenance burden of hand-tracking what the pipeline used to do, none of its guarantee that logic stays in sync if the DAC's attributes change later. If you need meaningful business logic applied, use the cache and pay the cost, or fully own bypassing it and document exactly why in the code - don't half-bypass it.

// Deliberate PXDatabase bypass: this is a one-time migration script for
// legacy transaction history with no GL, inventory, or audit side effects
// expected - those systems were already reconciled independently before
// this import. Do not reuse this pattern for live transaction entry.
PXDatabase.Insert<INTranHistoryArchive>(assignments);

The performance gap, roughly, from projects I've actually profiled

On a mid-size DAC with a handful of attached extensions, I've measured cache-driven inserts running anywhere from ten to fifty times slower than PXDatabase.Insert for the same row count, almost entirely attributable to event pipeline overhead rather than the SQL itself. That gap is exactly why bulk import and data migration tooling reaches for PXDatabase - but it's also exactly the gap representing business logic you're choosing to skip, which is why the decision has to be deliberate, not a default.

Wrapping up

Cache.Insert buys you the whole framework - defaults, validation, cross-extension side effects - at real per-row cost. PXDatabase.Insert buys you raw SQL speed by skipping all of it, cache and event pipeline included. Reach for PXDatabase deliberately, for bulk loads and standalone tables you've confirmed have no dependent logic, document exactly why in the code, and never treat it as a drop-in faster version of a normal insert on a DAC that anything else in the system relies on firing its events.