17.1 Table & Form Caching Mechanisms

17.1 Table & Form Caching Mechanisms

Quick Answer: Dynamics 365 Finance and Operations employs a multi-tier caching architecture spanning the web client browser, the Application Object Server (AOS) middle tier, and Azure SQL Database. The foundational caching mechanism is governed by the table metadata property CacheLookup. For single-record lookups, FoundAndEmpty caches both found records and missing keys to eliminate redundant round-trips for optional records, while EntireTable preloads all records into AOS memory on the first read. However, EntireTable carries a severe write penalty: any insert, update, or delete purges and invalidates the entire table cache across all AOS nodes in the cluster. Enterprise objects shared across user sessions and synchronized across multiple AOS instances must leverage SysGlobalObjectCache (SGOC) rather than single-server SysGlobalCache. On user interface forms, grid scrolling lag caused by N+1 display method execution is eliminated using declarative [SysClientCacheDataMethodAttribute(true)] or programmatic formDataSource.cacheAddMethod().

---

1. Multi-Tier Caching Architecture in Dynamics 365

In high-volume cloud ERP deployments, network latency between distributed tiers represents the single largest bottleneck for interactive forms and batch jobs. Dynamics 365 Finance and Operations organizes memory and data access across three distinct tiers:

Dynamics 365 Multi-Tier Caching Hierarchy

┌─────────────────────────────────────────────────────────────────────────────┐
│                            Web Client Tier (Browser)                        │
│  • Form control states, static UI metadata, user personalization            │
│  • Client-side display method value cache (SysClientCacheDataMethodAttribute)│
└──────────────────────────────────────┬──────────────────────────────────────┘
                                       │ HTTP / WebSockets (WAN Latency)
                                       ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                     Application Object Server (AOS) Tier                    │
│  • Single-Record Table Cache (None, NotInTTS, Found, FoundAndEmpty)         │
│  • Set-Based Table Cache (EntireTable cache in AOS memory)                   │
│  • RecordViewCache (Record buffer sets)                                     │
│  • SysGlobalCache (Session/AOS level) & SysGlobalObjectCache (SGOC)         │
└──────────────────────────────────────┬──────────────────────────────────────┘
                                       │ TDS Protocol (TCP 1433, LAN Latency)
                                       ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                         Azure SQL Database Tier                             │
│  • SQL Buffer Pool (Data & index pages in memory)                           │
│  • SQL Plan Cache (Compiled execution plans)                                │
│  • Read Committed Snapshot Isolation (RCSI) tempdb version store            │
└─────────────────────────────────────────────────────────────────────────────┘

Latency and Throughput Characteristics Across Tiers

Understanding where data resides determines the performance profile of custom X++ logic:

1. Web Client Tier: Executes in the user's browser. It caches static UI definitions, control state transitions, personalization settings, and display method values registered for client caching.
2. AOS Tier: The business logic engine running on .NET Core. It manages local memory caches for tables, queries, metadata, and cross-session application objects. When a query is satisfied by an AOS cache, zero network packets travel to the database.
3. Azure SQL Database Tier: The persistent relational data store. SQL Server manages its own internal buffer pool and plan cache. However, every query that leaves the AOS still incurs TCP/IP network latency, query compilation overhead, and thread scheduling on the database tier.

---

2. Table CacheLookup Property Deep Dive

The CacheLookup property on AOT Tables controls how the AOS caches individual records or complete table datasets. Single-record caching is indexed strictly by the table's Primary Index (which must be unique).

Critical Technical Nuances of CacheLookup

The Mechanics of FoundAndEmpty

When an application looks up a customer discount code or sales tax exemption rule in FoundAndEmpty mode and no matching row exists, the AOS places a negative cache marker in memory for that key. If that missing key is queried 5,000 more times during batch invoice processing, all 5,000 queries are resolved immediately from AOS memory without generating a single SQL query. Under Found, each of those 5,000 misses would trigger a distinct SQL SELECT statement, severely saturating database threads.

The Invalidation Penalty of EntireTable

EntireTable caching provides near-instantaneous reads because the entire dataset is loaded into AOS memory as a hash set. However, it carries a severe write penalty:

• Any write operation (insert(), update(), delete()) on an EntireTable table immediately flushes and invalidates the entire cache for that table, and the AOS notifies the other AOS instances that they must flush their copies too.
• Independently of any write, the AOS flushes every EntireTable cache once every 24 hours, so the first read after a flush always pays the full reload cost.
• A separate cache is held per table per company. Two selects on the same table for different DataAreaId values cache the table twice, which multiplies the memory footprint in multi-legal-entity deployments.
• If a developer configures EntireTable on a table containing 10,000 records that undergoes continuous background updates, the AOS will repeatedly purge and reload all 10,000 rows, consuming enormous CPU and memory while degrading SQL performance.
• One further quirk is worth memorising: if a select carrying the firstOnly qualifier is satisfied from an EntireTable cache, firstOnly is ignored.

[!IMPORTANT] Primary Index Prerequisite for Single-Record Caching Single-record caching (NotInTTS, Found, FoundAndEmpty) functions only when queries filter by an exact equality match on the table's unique Primary Index. If a query searches by secondary keys, non-unique fields, or uses range operators (>, <, like), single-record AOS caching is bypassed, and the query is forwarded directly to Azure SQL Database.

---

3. Advanced Cache Scopes in X++

Beyond declarative table properties, X++ provides programmatic caching scopes for granular memory management:

X++ Advanced Caching Scopes & Lifecycles

┌───────────────────────────────┐               ┌───────────────────────────────┐
│        SysGlobalCache         │               │     SysGlobalObjectCache      │
│     (appl.globalCache())      │               │            (SGOC)             │
├───────────────────────────────┤               ├───────────────────────────────┤
│ • Scope: Single AOS instance  │               │ • Scope: Multi-AOS / All Users│
│ • Per-session or AOS-wide     │               │ • Cross-session shared cache  │
│ • Not synchronized across AOS │               │ • Synchronized cache flush    │
│ • Cleared on AOS restart      │               │ • Ideal for compiled metadata │
└───────────────────────────────┘               └───────────────────────────────┘

RecordViewCache

RecordViewCache caches a result set — a group of records defined by one select — in AOS memory for the duration of the process that created it. The API is narrower than developers expect, and the exam tests the exact instantiation ritual:

1. Issue a select that carries the nofetch qualifier. nofetch tells the kernel to define the result set without materialising rows yet.
2. Pass the record buffer — not a Query object — to the RecordViewCache constructor.
3. Issue ordinary select statements afterwards. Provided a statement reads the same table and its where clause matches the clause the cache was built with, the kernel serves it from the cache. There is no cursor method to call; the cache is transparent.

/// <summary>
/// Demonstrates RecordViewCache for repeated read operations on a record subset.
/// </summary>
public static void processCachedCustomerTransactions(CustAccount _accountNum)
{
    CustTrans       custTrans;
    RecordViewCache recordViewCache;

    // 1. Define the result set. 'nofetch' is mandatory.
    select nofetch custTrans
        where custTrans.AccountNum == _accountNum;

    // 2. Cache the result set by passing the buffer itself.
    recordViewCache = new RecordViewCache(custTrans);

    // 3. Later selects that match the cached where clause are served from AOS memory.
    select firstonly custTrans
        where custTrans.AccountNum == _accountNum
           && custTrans.CurrencyCode == 'USD';
}

Hard constraints the exam leans on:

• If the instantiating select joins another table, targets a temporary table, or omits nofetch, the cache is not created and the kernel reports an error.
• The instantiating where clause accepts only == predicates. Ranges, wildcards, and inequality operators disqualify the statement.
• A cached statement must not itself participate in a join.
• The cache stores records in a linked list and is searched sequentially, so it suits modest result sets read many times, not large scans. An order by on a reading select causes the runtime to build a temporary index over the cache.
• forUpdate on the instantiating select locks the whole result set; use it only when every cached record really will be updated.

SysGlobalCache (appl.globalCache())

Provides an in-memory key-value dictionary scoped to either the current user session or the current AOS instance:

• Accessed via appl.globalCache() (AOS-wide) or infolog.globalCache() (user session).
• Fast, but does not synchronize across multiple AOS instances in a load-balanced cloud farm. If AOS-1 updates a value in appl.globalCache(), AOS-2 remains completely unaware of the change.

SysGlobalObjectCache (SGOC)

The enterprise caching infrastructure in Dynamics 365:

• Cross-Session and Multi-AOS: Cached items are available across all user sessions and synchronized across all active AOS instances.
• Cache Scope & Invalidation: SGOC partitions data by Scope (a string identifying the domain or table) and Key (a container identifying the object). When a record changes, calling SysGlobalObjectCache::clear(scope) invalidates the cached object across all AOS nodes via database-driven cache synchronization tokens.

---

4. The Singleton Design Pattern in X++

In Dynamics 365 Finance and Operations, parameter tables (such as CustParameters, VendParameters, InventParameters) consist of a single record per legal entity (DataAreaId). These tables must always be accessed using the Singleton Design Pattern paired with caching.

Standard Parameter Table Singleton Implementation

/// <summary>
/// Singleton implementation for parameter table retrieval with AOS caching.
/// </summary>
public final class CustParameters extends common
{
    /// <summary>
    /// Finds the singleton record for the current legal entity.
    /// </summary>
    /// <param name = "_forupdate">Determines whether the buffer is selected for update.</param>
    /// <returns>A CustParameters record buffer.</returns>
    public static CustParameters find(boolean _forupdate = false)
    {
        CustParameters parameter;

        if (_forupdate)
        {
            parameter.selectForUpdate(true);
            select firstonly parameter
                index Key
                where parameter.Key == 0;
        }
        else
        {
            // Relies on EntireTable caching configured on the table metadata
            select firstonly parameter
                index Key
                where parameter.Key == 0;
        }

        if (!parameter)
        {
            Company::createParameter(parameter);
        }

        return parameter;
    }
}

• Parameter tables set CacheLookup = EntireTable.
• The Key field is a fixed integer (always 0) enforcing a single row via an alternate key index.
• When _forupdate is false, the record is retrieved directly from AOS memory without touching Azure SQL.

---

5. Caching Form Display Methods

Display methods calculate values dynamically on forms (e.g., retrieving customer credit balance or formatting aggregated inventory). When placed on a grid, an uncached display method executes once per row, per column, on every redraw and scroll event—generating a catastrophic N+1 query pattern.

Uncached vs. Cached Form Display Method Execution Flow

Uncached Display Method (Per Row Render):
[Form Grid Scroll] ──> [Row 1: Call Server] ──> [SQL Query]
                   ──> [Row 2: Call Server] ──> [SQL Query]  (N+1 Chattiness)
                   ──> [Row 3: Call Server] ──> [SQL Query]

Cached Display Method (Client Tier Cache):
[Record Fetch]     ──> [Batch Call Server]  ──> [Values Cached on Client]
[Form Grid Scroll] ──> [Read Client Memory] (Zero RPC, Instant Smooth Scroll)

Declarative Caching: [SysClientCacheDataMethodAttribute]

Decorate the display method definition directly on the table or form datasource:

/// <summary>
/// Calculates the customer open balance and caches the result on the client tier.
/// </summary>
/// <returns>The total open customer balance in MST.</returns>
[SysClientCacheDataMethodAttribute(true)]
public display AmountMST displayOpenBalance()
{
    return CustTrans::calcOpenBalance(this.AccountNum);
}

Programmatic Caching: formDataSource.cacheAddMethod

When modifying existing forms without altering table metadata, register the method programmatically in the form datasource's init() method:

[ExtensionOf(formDataSourceStr(CustTable, CustTable))]
final class CustTableForm_CustTableDS_Extension
{
    public void init()
    {
        next init();

        FormDataSource custTable_ds = this;
        // Registers the display method to be pre-calculated and cached on row fetch
        custTable_ds.cacheAddMethod(tableMethodStr(CustTable, displayOpenBalance));
    }
}

Cache Invalidation on Forms

When an underlying field changes on the form that affects the calculated display value, developers must explicitly invalidate the cached calculation for that record:

// Invalidates cached display value and forces recalculation for current row
custTable_ds.cacheCalculateMethod(tableMethodStr(CustTable, displayOpenBalance));

---

6. Realistic Enterprise Scenario: High-Throughput Pricing & Discount Engine

Business Problem

A multinational distributor processes 400,000 sales order lines daily across six regional distribution centers. During peak ordering hours, sales order entry forms experience severe lag, and database CPU reaches 92%. Inquiries using SQL telemetry and Trace Parser reveal that 85% of incoming queries search for customer-specific price overrides in CustSpecialPricingDiscount, where over 80% of queried customer accounts have no override record. Furthermore, order entry clerks report that scrolling through the sales order lines grid stutters noticeably, generating thousands of Remote Procedure Calls (RPCs) per minute.

Architecture & Implementation Walkthrough

1. Eliminating Lookup Misses via FoundAndEmpty: The development team examines CustSpecialPricingDiscount. The table has CacheLookup set to Found. Because 80% of customer lookups miss, the AOS was issuing hundreds of thousands of SQL SELECT statements searching for non-existent records. By changing CacheLookup to FoundAndEmpty, the AOS caches both successful lookups and negative misses. Repeated misses for standard customers are resolved instantly in AOS memory.
2. Parameter Table Optimization via EntireTable Singleton: The team verifies that module parameter tables like SalesParameters are accessed using SalesParameters::find(). The table metadata is confirmed to have CacheLookup = EntireTable, ensuring that parameter reads incur zero database queries across all user sessions.
3. Eliminating Form Grid Lag via Display Method Caching: The sales order line grid features a display method displayAvailablePhysical() on SalesLine. An extension class is implemented to decorate this method with [SysClientCacheDataMethodAttribute(true)], and salesLine_ds.cacheCalculateMethod() is hooked to the ItemId and InventDimId modified events.

Measurable Outcomes

• Database CPU utilization dropped from 92% to 28% during peak hours.
• SQL statement round-trips for pricing checks decreased by 94%.
• Form grid scrolling latency on the sales order entry screen dropped from 850 ms per page down to 12 ms, completely eliminating UI stutter.

---

7. Real-World MB-500 Exam Traps

[!WARNING] Exam Trap 1: Misconfiguring EntireTable on Volatile Datasets A favorite MB-500 scenario asks whether a table tracking real-time delivery container statuses (5,000 rows, updated every 30 seconds by automated scanners) should use EntireTable caching. The answer is an emphatic NO. Any update to an EntireTable cache forces the AOS to broadcast an invalidation token across all AOS instances in the cluster, forcing every server to re-read and reload the entire table. EntireTable is strictly for small, static reference datasets that change rarely — and "small" means a cached footprint under 128 KB, past which the cache spills to disk and the optimisation reverses itself.

[!WARNING] Exam Trap 2: Believing Single-Record Caching Operates on Secondary Keys Candidates often assume that setting CacheLookup = Found will speed up queries filtering by CustTable.CustGroup or CustTable.Currency. In Dynamics 365, single-record caching (NotInTTS, Found, FoundAndEmpty) is active only when queries filter by an exact equality match on the table's unique Primary Index. Queries using non-primary keys, range expressions (>, <), or while select scans completely bypass AOS table caching.

[!WARNING] Exam Trap 3: Confusing SysGlobalCache with SysGlobalObjectCache (SGOC) In an exam scenario involving a load-balanced cloud deployment with multiple AOS instances, questions will test whether appl.globalCache() can synchronize cached authorization tokens across all servers. It cannot! appl.globalCache() is strictly local to a single AOS instance. To share and synchronize cached objects across all users and all AOS nodes, you must use SysGlobalObjectCache (SGOC).

[!WARNING] Exam Trap 4: Forgetting cacheCalculateMethod After Modifying Underlying Data If a cached display method calculates a customer's credit score based on their credit limit, and user code modifies the credit limit on the form, the displayed value will remain stale until formDataSource.cacheCalculateMethod(tableMethodStr(...)) is explicitly called to invalidate the client-side cache for that row.

[!WARNING] Exam Trap 5: Expecting NotInTTS to Cache Inside Active Transactions As the name implies, NotInTTS caching operates only outside active transaction blocks. Once ttsbegin executes, any query against a NotInTTS table bypasses the AOS cache and queries Azure SQL Database directly to ensure transactional code reads the latest committed state.