CacheLookup & Table Caching in Dynamics 365 Finance & Operations
Question 14 — Record caching modes, how they map to table groups, and how caching boosts performance.
Interview Question
Model Answer (Short)
CacheLookup is a table property that controls how records are cached to reduce round trips to
SQL Server. There are five modes — None, Found,
FoundAndEmpty, NotInTTS and EntireTable — and the right one
depends on the table's TableGroup and how the data is read. Caching only kicks in when a
SELECT queries on the entire primary key (or a unique index) with an equality match.
Choosing the correct mode gives big performance wins for reference and parameter data, while transactional tables
are usually left uncached.
The Five Caching Modes
Modes explained
- None — no caching; every read goes to the database. Used for transactional tables where data changes constantly.
- Found — caches records that are found. Once a record is read on its full primary key, it is served from cache next time. Good for relatively static data read by key.
- FoundAndEmpty — caches both found records and the fact that a key returned no record (the "empty" result), avoiding repeated lookups for missing keys.
- NotInTTS — the record is cached, but inside a transaction (
ttsbegin…ttscommit) it is always re-read from the database with a lock to guarantee a fresh value. Typical for transaction-style tables. - EntireTable — the whole table is loaded into a set-based cache on the AOS. Ideal for small, rarely-changing parameter tables.
Recommended CacheLookup by Table Group
| Table group | Recommended CacheLookup |
|---|---|
| Miscellaneous | None |
| Parameter | EntireTable |
| Group | Found or FoundAndEmpty |
| Main | Found or FoundAndEmpty |
| Transaction | NotInTTS |
| WorksheetHeader | NotInTTS |
| WorksheetLine | NotInTTS |
| Worksheet | NotInTTS |
| TransactionHeader | NotInTTS |
| TransactionLine | NotInTTS |
When is the cache actually used?
- The
SELECTmust query on the complete primary key (for Found / FoundAndEmpty / NotInTTS) with an equality condition. - For EntireTable, any
SELECTcan be served because the whole table sits in memory. - Caching is bypassed for queries using ranges, joins on partial keys, or non-key fields.
- The cache is company (dataArea) specific and lives on the AOS.
Prerequisites (Rule 5)
- Visual Studio with the Dynamics 365 developer tools.
- A custom model / package for your objects.
- A table with a well-defined primary key / unique index.
- Correctly set TableGroup so the recommended cache mode is meaningful.
Code Example — A cache-friendly find method
Selecting on the full primary key lets the record cache serve the result:
public static AbcCustomerCategory find(AbcCategoryId _categoryId,
boolean _forUpdate = false)
{
AbcCustomerCategory category;
category.selectForUpdate(_forUpdate);
// Equality match on the COMPLETE primary key -> cache can be used
select firstonly category
where category.CategoryId == _categoryId;
return category;
}A query that will NOT use the cache
AbcCustomerCategory category;
// Range / non-key filter -> bypasses Found / FoundAndEmpty cache
while select category
where category.Description like 'Gold*'
{
// served from the database, not the record cache
}Flushing the cache after data changes
// Clear the AOS cache for a table (e.g. after a bulk data load)
AbcCustomerCategory category;
category.cacheClear(); // invalidates cached records for this table bufferPoints the interviewer wants to hear
- Five modes: None, Found, FoundAndEmpty, NotInTTS, EntireTable.
- EntireTable suits small parameter tables; NotInTTS suits transactional tables.
- Found/FoundAndEmpty caching only works on a complete primary key equality query.
- FoundAndEmpty also caches "not found" results to avoid repeat lookups.
- Caching is company-specific and set via the table's CacheLookup property.
Likely Follow-up Questions
- Why is
NotInTTSthe safe choice inside a transaction? - What is the difference between
FoundandFoundAndEmpty? - Why won't a query with a range filter use the record cache?
- When should you avoid
EntireTablecaching?
Key Takeaway
CacheLookup trades memory for speed by serving records from the AOS cache instead of SQL Server. Match the mode to the table group — EntireTable for parameters, Found/FoundAndEmpty for main/group data, and NotInTTS for transactions — and remember caching only fires on full primary-key equality reads.