Single-record Caching

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

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 record buffer disableCache method has not been called with a parameter of true.

Records are cached for all unique indexes when all the following criteria are met:

• The table is cached by having its CacheLookup property set 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 any unique index.

Cache Location

Caches are used on both the client and the Application Object Server (AOS). 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 is not located in the server cache, it is retrieved from the database. You can configure the number of records to be maintained in the cache by using the Server configuration form. The form is found in the application workspace at:
 System administration > Area page > Setup > System > Server configuration. In this form, continue to:
 Performance Optimization > Performance Settings > Client record cache factor.

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 server-side cache, the record is retrieved from the database. You can configure the maximum number of records that are maintained in a server cache by using the Cache Limits section of the Server configuration form. The default value is 2000 records for a given company per table group or per Application Object Server (AOS).

Configuring the Cache Sizes

You can configure the size of the cache by navigating to the Server configuration form. This form is found in the application workspace at:
 System administration > Area page > Setup > System > Server configuration.

On the form, the cache configuration items are at:
 Performance Optimizations > Performance Settings.

There the cache settings can be modified for all instances of the AOS, or for only the current instance of the AOS.

Enhanced Caching Functionality

The following table describes enhanced caching features that were added in Microsoft Dynamics AX 2012.

CacheLookup Property

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

Transaction Boundaries for Caching

The Found and FoundAndEmpty caches across 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) // X++, in AOT > Jobs.
    {
        CustTable custTable;
        ;
        // The query looks for a record in the cache.
        // If the record does not exist in the cache,
        // 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 a record is 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’s 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.

Code Example to Demonstrate Performance

The following X++ code example is a job that selects one record from the CustTable table numerous times in two loops. Caching is disabled for the first loop by a call to the disableCache method. Caching is enabled for the second loop. The comment at the end of the code displays the timed results. The results show that the first loop took 148 seconds to complete, whereas the second loop took only 1 second.

    static void SingleRecordCachePerfTest3Job(Args _args) // X++, under AOT > Jobs.
    {
        /***
           PROPERTIES OF CUSTTABLE TABLE (in Microsoft Dynamics AX 2012):
        Field AccountNum is Mandatory (not nullable).
        Field Party is Mandatory (not nullable).
        Unique index AccountIdx is on one field, AccountNum.
        Unique index Party is on one field, Party.
        Property PrimaryIndex = AccountIdx (just on AccountNum).
        Property CacheLookup = Found.
        ***/
        
        CustTable tabCust;
        str 10 singletonValue = "4001"; // tabCust.AccountNum field value.
        //int64 singletonValue  = 987654321; // tabCust.Party field value.
        int nLoops = 65536;
        int i;
        int nSecsSinceMidnight[4];
    
        // Prime the demo with one retrieval.
        select
        from
            tabCust
        where
            tabCust.AccountNum == singletonValue; // Unique index on this field.
    
    
        tabCust.disableCache(true); // do Not use the cache.
        
        nSecsSinceMidnight[1] = timeNow();
        for (i=0; i < nLoops; i++)
        {
            select
                from
                    tabCust
                where
                    tabCust.AccountNum == singletonValue; // Unique index on this field.
        }
        nSecsSinceMidnight[2] = timeNow();
        
        tabCust.disableCache(false); // Do use the cache!
        
        for (i=0; i < nLoops; i++)
        {
            select
                from
                    tabCust
                where
                    tabCust.AccountNum == singletonValue;
        }
        nSecsSinceMidnight[3] = timeNow();
        
        info( strFmt("Time1: %1", nSecsSinceMidnight[1] ));
        info( strFmt("Time2: %1", nSecsSinceMidnight[2] ));
        info( strFmt("Time3: %1", nSecsSinceMidnight[3] ));
    }
    /*****  Infolog display
    Message (07:44:11 am)
    Time1: 31301
    Time2: 31449
    Time3: 31450
    *****/

In the preceding X++ job, caching occurs because there is a unique index on the AccountNum field.

Message (08:56:13 am) 
 Time1: 32108 
 Time2: 32146 
 Time3: 32183

Client and AOS CPU Workloads

In the preceding code example there are two loops. During the first loop when the cache is not being used, the central processing unit (CPU) of the server computer is very busy with processing for the AX32serv.exe AOS process. During the second loop when the cache is being used, the CPU of the client computer is very busy with processing for the AX32.exe client process.

See also

Record Caching

Set-based Caching

Table Properties

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.