Single-Record Caching

Record caching is enabled for a table when all the following statements are true:

  • The CacheLookup property on the table is enabled by setting it to one of the following values:

    • notInTTS

    • Found

    • FoundAndEmpty

  • The table's PrimaryIndex property is set to a unique index that exists on the table. The RecId index does not qualify as a caching index unless you set the table's PrimaryIndex property to this index.

  • The record buffer disableCache method has not been called with a parameter of true.

The fields in the table's unique index make up the caching key. A record is placed in the cache when the following criteria are met:

  • The table is cached by setting the CacheLookup property to notInTTS, Found, or FoundAndEmpty.

  • The SELECT statement that selects the records uses an equal operator (==) on the caching key. The fields in the WHERE clause of the SELECT statement match the fields in the index referenced by the table's PrimaryIndex property.

The table's CacheLookup property defines how and when records are cached as shown in the following table.

CacheLookup Property Value

Result

None

No data is cached or retrieved from the cache for this table.

This property value should be used for tables that are heavily updated or where it's unacceptable to read outdated data.

NotInTTS

All successful caching key selects are cached.

When in a transaction (after ttsBegin), no caches made outside the transaction are used. When inside a transaction, the record is read once from database and subsequently from cache. The record is select-locked when read in a transaction, which ensures that the record cached is not updated while the transaction is active.

A typical example of the NotInTTS property is the CustTable in the Microsoft Dynamics AX standard application. It's acceptable to read outdated data from the cache outside a transaction, but when data is used for validation or creating references, it is ensured that the data is real-time.

Found

All successful caching key selects are cached. All caching key selects are returned from the cache if the record exists there. A selectforUpdate in a transaction forces reading from the database and replaces the record in the cache.

This is typically used for static (lookup) tables, such as Unit, where the record usually exists.

FoundAndEmpty

All selects on caching keys are cached, including selects that are not returning data.

All caching key selects are returned from caching if the record exists there, or the record is marked as nonexistent in the cache. A selectforUpdate in a transaction forces reading from the database and replaces the record in the cache.

An example of FoundAndEmpty record caching is in the Discount table in the Microsoft Dynamics AX standard application. By default, the Discount table has no records. By using a FoundAndEmpty cache on this table, the keys that are queried for but not found are stored in the cache. Subsequent queries for these same non-existent records can be answered from the cache without a round trip to the database.

EntireTable

Creates a set-based cache on the server. The entire table is cached as soon as at least one record is selected from the table.

The Found and FoundAndEmpty caches cross transaction boundaries. The NotInTTS cache is newly created inside a transaction. The following code example is from Greef, Pontoppidan, et al. 2006. Inside Microsoft Dynamics AX 4.0. 445. Redmond: Microsoft Press. This example, modified for the purposes of this topic, demonstrates how records are retrieved from the cache when the table's CacheLookup property is set to NotInTTS, and the PrimaryIndex property is set to a unique index on the AccountNum field.

static void NotInTTSCache(Args _args)
{
    CustTable custTable;
    ;
// The query looks for records in the cache.
// If records don't exist, the query accesses the database.
    select custTable
        where custTable.AccountNum == '4000';
    // The transaction starts.
    ttsbegin;
    // The cache is not used. The query accesses the database
    // and records are placed in the cache.
    select custTable
        where custTable.AccountNum == '4000';

    // The query uses the database because 
    // the forupdate keyword is used.
    select forupdate custTable
        where custTable.AccountNum == '4000';
    // The query uses the cache and not the database.
    select custTable
        where custTable.AccountNum == '4000';
    // The query uses the cache because
    // the forupdate keyword was used previously.
    select forupdate custTable
        where custTable.AccountNum == '4000';

    // The transaction is committed.
    ttscommit;

    // The query will use the cache.
    select custTable
        where custTable.AccountNum == '4000'; 
}

Reproduced by permission from Greef, Pontoppidan, et al, Inside Microsoft Dynamics AX 4.0 (Redmond, WA: Microsoft Press, 2006), 445.

If the table CacheLookup property was set to Found or FoundAndEmpty, the first select statement inside the transaction (after the TTSBegin statement) would retrieve the record from the cache.

Cache Location

Caches are used on both the client and the server. The Microsoft Dynamics AX runtime manages the cache by removing old records when new records are added to the cache.

Client Cache

A client-side cache can be used only by the client. The client cache is used when a select is executed from the client tier. If no record is found in the client cache, the client then searches the server cache for the record. If the record isn't located in the server cache, it's retrieved from the database. The maximum number of records maintained in a client cache is 100 records per table for a given company.

Server Cache

A server-side cache can be used by any connection to the server. The server cache is used when a select is executed on the server tier. If no record is found in the cache, it's retrieved from the database. The maximum number of records maintained in a server cache is 2,000 records per table for a given company.

See Also

Record Caching

Set-Based Caching