17.1 Table & Form Caching Mechanisms

Microsoft Dynamics 365: Finance and Operations Apps Developer Associate MB-500

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Cloud & Ecosystem Architecture

1.1 Cloud vs On-Premises Architecture & Deployment Typologies 1.2 Microsoft Ecosystem Integration & Extensibility Boundaries 1.3 Lifecycle Services (LCS) & Implementation Portal

2 Chapter 2: ALM, Environments & Unified Developer Experience

2.1 Package Deployment & Servicing 2.2 Unified Developer Experience (UDE) & Power Platform Admin Center 2.3 Environment Management & Governance

3 Chapter 3: Visual Studio Development & Metadata

3.1 Visual Studio Extension Models & Packages 3.2 Application Explorer & Element Designers 3.3 Database Synchronization & Visual Studio Debugging

4 Chapter 4: Azure DevOps Version Control & CI/CD

4.1 Version Control Architecture: TFVC vs Git in F&O 4.2 Branching, Merging & Conflict Resolution 4.3 CI/CD Build Pipelines & Artifact Generation

5 Chapter 5: UI Elements: Forms, Menus & Labels

5.1 Form Design & Form Patterns 5.2 Menus, Menu Items & Navigation 5.3 Label Files & Localization

6 Chapter 6: Data Model: Tables & Relational Design

6.1 Table Design & Extension 6.2 Views, Queries & Maps 6.3 Data Entity Design & Modeling

7 Chapter 7: Classes, Attributes & AOT Modifiers

7.1 Class Architecture & Access Modifiers 7.2 Attributes & Reflection 7.3 Event Handlers & Pre/Post Method Events

8 Chapter 8: X++ Core Programming & CRUD Operations

8.1 X++ Syntax, Control Flow & Global Functions 8.2 Data Manipulation & CRUD Logic 8.3 Best Practice Rules & Compiler Diagnostics

9 Chapter 9: Advanced OOP, Chain of Command & Extensibility

9.1 OOP Principles & QueryBuilder Class 9.2 Chain of Command (CoC) Implementation 9.3 Extensibility Patterns & Non-Breaking Changes 9.4 Event Handler Classes & Delegate Architecture 9.5 Variable Scoping, Declaration Placement & Object Lifetime

10 Chapter 10: F&O Frameworks & SysTest Unit Testing

10.1 SysOperation Framework Architecture 10.2 Workflow Framework & SysExtensionSerializer 10.3 SysTest Framework & Test Explorer

11 Chapter 11: Reporting Architecture & SSRS Reports

11.1 Reporting Architecture & Datastores 11.2 Report Data Providers & Contracts 11.3 SSRS Report Design, Deployment & Security 11.4 Determining When to Use a Specific Reporting Tool or Platform

12 Chapter 12: Business Intelligence, Workspaces & Electronic Reporting

12.1 Electronic Reporting (ER) Framework 12.2 Power BI Integration & Analytical Workspaces 12.3 Workspaces, Tiles, Lists & KPIs 12.4 Excel Integration & Office App

13 Chapter 13: Integration Patterns & Service Endpoints

13.1 Integration Pattern Evaluation & API Selection 13.2 Custom Services & SOAP/REST Endpoints 13.3 Consuming External Services & REST/SOAP APIs

14 Chapter 14: Data Management Framework (DMF) & Batch Processing

14.1 DMF Concepts, Projects & Sequencing 14.2 Entity Change Tracking & Recurring Integrations 14.3 Composite Entities & Batch OData API 14.4 Monitoring Entity Status, Availability & Job Execution

15 Chapter 15: Power Platform, Business Events & Azure Integrations

15.1 Dual-Write & Virtual Entities 15.2 Business Events Framework 15.3 Azure Key Vault Integration & Secure Storage

16 Chapter 16: Role-Based Security & Extensible Data Security

16.1 Role-Based Security Architecture 16.2 Extensible Data Security (XDS) Policies 16.3 Security Enforcement & Best Practices

17 Chapter 17: Performance Optimization & Data Access Patterns

17.1 Table & Form Caching Mechanisms 17.2 Temporary Tables & Set-Based Operations 17.3 Query Tuning, Indexes & Concurrency 17.4 Diagnostics: Trace Parser, Async & Sandbox Frameworks 17.5 Tuning Variable Scope, Buffer Lifetime & Memory Pressure

17.1 Table & Form Caching Mechanisms

Key Takeaways Explain This Topic AI

Table caching in Dynamics 365 Finance and Operations operates primarily in the AOS memory tier to eliminate expensive network round-trips to Azure SQL Database.

The Table CacheLookup property controls single-record caching: None disables caching, NotInTTS caches outside transactions, Found caches existing records, FoundAndEmpty caches misses to prevent repeated SQL round-trips, and EntireTable loads the complete table on first read.

Tables configured with CacheLookup = EntireTable must be restricted to small, static datasets: Microsoft's documented boundary is a 128 KB cache size, beyond which the cache spills from memory to disk, and any insert, update, or delete flushes the cache on every AOS instance.

SysGlobalObjectCache (SGOC) provides a multi-user, multi-AOS synchronized cache for shared application objects and metadata, contrasting with the single-session, single-AOS scope of SysGlobalCache (appl.globalCache()).

Display methods on form grids cause severe N+1 query bottlenecks; applying [SysClientCacheDataMethodAttribute(true)] or invoking formDataSource.cacheAddMethod() caches computed values on the client tier during scrolling.

Last updated: September 2026

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:

Caching Tier Physical Location Typical Access Latency Invalidation Scope Primary Architectural Benefit

Web Client Cache Browser memory / DOM < 0.1 ms Active browser session / form lifecycle Eliminates HTTP network chatter during grid scrolling and tab switching.

AOS Table Cache AOS Worker Process RAM (.NET Core) 0.05 – 0.2 ms Cluster-wide or per-AOS node Eliminates Tabular Data Stream (TDS) round-trips to Azure SQL Database.

Azure SQL Buffer Pool Database server RAM 0.5 – 2.0 ms Managed by SQL Server storage engine Avoids physical SSD disk I/O, but still incurs network transmission latency.

Azure SQL Disk I/O Premium Azure SSD Storage 5.0 – 25.0 ms N/A (Persistent storage) High-latency physical read operations when buffer pool misses occur.

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.

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.

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).

CacheLookup Value Cache Mechanism Behavior on Cache Miss TTS Transaction Behavior Recommended Table Profile

None No AOS caching Every query hits Azure SQL Reads always hit Azure SQL High-volume transactional tables (e.g., SalesLine , InventTrans , GeneralJournalAccountEntry ).

NotInTTS Single-record cache Reads from SQL and caches on AOS Cache completely bypassed inside ttsbegin / ttscommit blocks; reads hit SQL directly Master entities where updates inside transactions must read the latest committed SQL state.

Found Single-record cache Reads from SQL; caches record only if found Active both inside and outside TTS Master tables where keys are stable and records almost always exist (e.g., CustTable , VendTable ).

FoundAndEmpty Single-record cache Reads from SQL; caches both found records and misses Active both inside and outside TTS Tables queried frequently for optional records or sparse mappings (e.g., tax exemptions, discount rules).

EntireTable Full table set cache Preloads all records into AOS memory on the first read Uses cached memory copy; write operations flush entire cache across all AOS nodes Small, static parameter and configuration tables. Microsoft's documented boundary is cache size , not row count: once the cached set exceeds 128 KB the cache spills from memory to disk and reads slow down sharply.

• 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:

Issue a select that carries the nofetch qualifier. nofetch tells the kernel to define the result set without materialising rows yet.

Pass the record buffer — not a Query object — to the RecordViewCache constructor.

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.

Advanced Caching API Scope & Lifetime Multi-AOS Synchronized? Thread Safety Recommended Use Case

infolog.globalCache() Single user session; cleared at session termination No Per-session single thread Transient state, wizard navigation buffers, and user-specific calculation contexts.

appl.globalCache() Single AOS instance; cleared at AOS service restart No Multi-threaded on single AOS AOS-local non-synchronized lookups and service connection handles.

SysGlobalObjectCache All AOS instances; cleared on explicit invalidation token Yes (Synchronized via DB) Thread-safe across cluster Compiled tax calculation rules, authorization matrices, and enterprise metadata lookups.

RecordViewCache Server-side; accessible only to the process that created it No (private to the creating process) Thread-local Multi-pass algorithms re-reading an identical, equality-defined record subset within a single batch job.

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

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.

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.

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.

Loading diagram... AOS Multi-Tier Cache Lookup Decision Tree