what they mean, when to use them, and what can bite you

In Dynamics 365 Finance & Operations and historically in AX, table caching is one of the biggest “cheap wins” for performance – if you pick the right cache mode for the right kind of data and understand the trade-offs.

The knob you turn in metadata is the table property CacheLookup (under properties on a table). Microsoft documents the property at a high level as controlling how records are cached during lookup/retrieval, with some important inheritance constraints.  You can think of CacheLookup as choosing how aggressively the kernel will reuse previously-read rows instead of round-tripping to SQL – going from the app all the way to and through SQL to get a response.

None

No record caching for this table. This is best used when the table is updated frequently (high churn) or reading slightly stale data is unacceptable or the access pattern is “wide” (many different keys) so caching doesn’t pay off. The penalty here is this the most chatty we can be with our database with every read fetching a record from the database. 

NotInTTS

This means cache single-record lookups but treat transactions as a boundary. In other words, the runtime avoids reusing cached values across transactional scopes in ways that could be risky. This is best used when the table is transactional-ish (often read and updated in the same logical unit of work) or you want caching benefits but safer behavior around transactions. This value is a best fit for transaction/worksheet-ish tables commonly align with this choice in Microsoft guidance.

Found

This means to cache successful single-record key lookups when a record was found. This is best used when you want to repeatedly look up the same records by a stable key (classic “master data” behavior), or the table changes infrequently relative to reads. This value typically fits in table groups Main/Group/Reference. 

FoundAndEmpty

Similar to Found, but it also caches “misses” also – so lookups that were found and also empty. This can reduce repetitive “does it exist?” database calls which you may see in legacy parts of the code with exist(). This is best used when the application frequently checks for optional configuration or conditional rows that often don’t exist or you see lots of repeated “exists-style” reads that usually return nothing. This comes with a trade-off of you can cache “non-existence,” which is great for performance but can be confusing if records get inserted later and you don’t expect caching behavior. Microsoft’s best-practice tooling explicitly treats FoundAndEmpty as a meaningful cache mode and flags certain table-group combinations as invalid.

EntireTable

This means set-based caching: once the table is accessed in a way that qualifies, the runtime can cache the whole table (per company/partition context), enabling reads to be served from memory rather than SQL. This is best used when the table is small (or effectively small per company/partition), it changes rarely, and it is hammered constantly (parameters/setup tables are the usual suspects). Microsoft’s own rules-of-thumb commonly map Parameter tables to EntireTable. However, there are some behaviors that are important for this cache option type. When the runtime can satisfy a select from the EntireTable cache, some select modifiers behave differently. For example, the Learn docs note that firstOnly can be ignored if the AOS uses the EntireTable cache to satisfy the request. Additionally, the X++ keyword flush clears the entire table cache, and Microsoft describes it as a way to ensure changes are reflected immediately. You’ll typically see a flush keyword used related to Parameter Tables. Also, there are inheritance-related restrictions around setting EntireTable from the tooling for inheritance root tables. This can be powerful but if you put EntireTable on something large or frequently updated, you can create nasty “cache rebuild” overhead and/or memory pressure.

Which One To Use

Microsoft’s published “basic rules” (originally for AX 2012, still conceptually useful – https://www.microsoft.com/en-us/dynamics-365/blog/no-audience/2015/02/20…) line up cache mode with TableGroup. In short: Parameters > EntireTable, master-ish tables > Found, transactional-ish tables > NotInTTS. Also, Microsoft’s Customization Analysis Report / CAR rules reinforce that certain combinations are suspicious—for example, Worksheet/Transaction table groups combined with Found, FoundAndEmpty, or EntireTable are flagged as a mismatch.

Ok, So Which One To Use?

In general, I map based on the following rules:

If table is for parameters > EntireTable

If table group is Main / Group / Reference > Found ( or sometimes FoundAndEmpty if knowing that something is not there is important)

If table group is Transaction / Worksheet > NotInTTS

If you’re not sure > start with the table group guidance above then validate with tracing and load testing.

Still unsure > None is not the answer. Whatever you’re building likely was based on something else so find that thing and use the cache lookup value from that thing.

A Few Gotchas

CacheLookup works best with “key-style” selects means looking for records based on a known key such as RecId or SalesId on SalesTable. Caching pays off most when your code repeatedly hits the same key (or same small set of keys). If you’re doing broad scans or complex joins, cache might not help much – or can even surprise you. Also depending on the table type, you may need to add code to flush tables from cache, using the flush keyword. You’ll frequently see this on OOTB Parameter forms with code snippet like “flush MyTable” sprinkled into the form.. If you need explicit or scoped set-based caching behaviors in code, there is class RecordViewCache exposed for use. More details at https://learn.microsoft.com/en-us/dotnet/api/dynamics.ax.application.rec…. Lastly, Microsoft documents flush as clearing the entire table cache but is syntactically correct for all tables – it may do nothing based on the table.

Wrapping Up

CacheLookup is one of those deceptively simple properties that can make your code way faster or mysteriously weird depending on how closely your table’s usage pattern matches the cache mode.