Performance, Caching & Set-Based Operations

Why performance tuning matters in F&O

Table caching

Cache types

F&O supports three table caching modes, configured on the table’s CacheLookup property:

When caching works (and doesn’t)

// ✅ Cache HIT — lookup by primary key (unique index)
select firstOnly custTable
    where custTable.AccountNum == '1001';

// ❌ Cache MISS — lookup by non-indexed field
select firstOnly custTable
    where custTable.CustGroup == 'RETAIL';

SysGlobalObjectCache

For custom caching needs beyond table-level caching, F&O provides SysGlobalObjectCache — a shared, in-memory key-value store across the AOS.

// Writing to global cache
SysGlobalObjectCache cache = new SysGlobalObjectCache();
container key = ['ExchangeRate', 'NZD', 'USD'];
container value = [1.62, today()];
cache.insert(classStr(MyExchangeRateHelper), 0, key, value);

// Reading from global cache
container cachedValue = cache.find(
    classStr(MyExchangeRateHelper), 0, key);
if (cachedValue != conNull())
{
    Real rate = conPeek(cachedValue, 1);
    // Use cached rate
}
else
{
    // Cache miss — fetch from source and cache it
}

Singleton pattern

For objects that should be instantiated once and reused (e.g. configuration readers), the singleton pattern avoids repeated construction:

class PFConfigReader
{
    private static PFConfigReader instance;
    private PFConfigTable configData;

    private void new()
    {
        // Private constructor — can't be instantiated directly
        select firstOnly configData from configData;
    }

    public static PFConfigReader getInstance()
    {
        if (!instance)
        {
            instance = new PFConfigReader();
        }
        return instance;
    }

    public PFConfigTable getConfig()
    {
        return configData;
    }
}

TempDB vs InMemory temp tables

Choosing the right temp table type has a significant performance impact:

Set-based operations

This is the single biggest performance win in X++ development. A row-by-row loop can take minutes; the equivalent set-based statement takes seconds.

Row-by-row vs set-based

// ❌ ROW-BY-ROW: Slow — one SQL statement per row
while select forUpdate salesLine
    where salesLine.SalesStatus == SalesStatus::Backorder
{
    salesLine.SalesStatus = SalesStatus::Open;
    salesLine.update();
}
// 10,000 rows = 10,000 individual UPDATE statements

// ✅ SET-BASED: Fast — one SQL statement for ALL rows
update_recordset salesLine
    setting SalesStatus = SalesStatus::Open
    where salesLine.SalesStatus == SalesStatus::Backorder;
// 10,000 rows = 1 UPDATE statement

The three set-based operations

insert_recordset example

// Copy all backorder lines from SalesLine to a staging table
insert_recordset pfStagingTable (ItemId, Qty, SalesId)
    select ItemId, SalesQty, SalesId
    from salesLine
    where salesLine.SalesStatus == SalesStatus::Backorder;

When set-based falls back to row-by-row

Certain conditions force the runtime to fall back to row-by-row processing, negating the performance benefit:

pfStagingTable.skipDataMethods(true);  // Skip insert/update overrides
pfStagingTable.skipEvents(true);       // Skip database events

update_recordset pfStagingTable
    setting Status = PFStatus::Processed
    where pfStagingTable.Status == PFStatus::Pending;

Query hints and performance

Concurrency control

When two users (or batch threads) try to update the same record simultaneously, F&O needs a strategy to prevent data loss or corruption.

Optimistic concurrency in action

// Optimistic: read record, user edits, save checks RecVersion
ttsBegin;
select forUpdate salesTable
    where salesTable.SalesId == 'SO-001';
// User edits fields...
salesTable.SalesStatus = SalesStatus::Confirmed;
salesTable.update(); // Checks RecVersion — throws if changed
ttsCommit;

// Handle conflict
catch (Exception::UpdateConflict)
{
    // Record was modified by another user since we read it
    if (appl.ttsLevel() == 0)
    {
        if (xSession::currentRetryCount() < 3)
        {
            retry;
        }
    }
}

Pessimistic locking

// Pessimistic: lock the record at read time
ttsBegin;
select pessimisticLock salesTable
    where salesTable.SalesId == 'SO-001';
// Record is now locked — no other session can update it
salesTable.SalesStatus = SalesStatus::Confirmed;
salesTable.update();
ttsCommit;
// Lock released on commit

Variable scoping and garbage collection

Large objects (like populated temp tables, buffers, containers) should be scoped as tightly as possible to allow the garbage collector to reclaim memory:

// ❌ Bad: large buffer lives for entire method
void processAllOrders()
{
    SalesTable salesBuf; // Alive for entire method
    // ... 500 lines of code ...
}

// ✅ Good: scope buffers to where they're needed
void processAllOrders()
{
    this.loadOrders();   // salesBuf scoped inside
    this.processLines(); // separate buffer scoped inside
}

Next up: Trace Parser & Optimization — capturing traces, analysing call trees, index strategy, and optimising batch processes.