Single-record Caching

Article
05/18/2015

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.

Note

To give an idea of scale, prior to Microsoft Dynamics AX 2012 the maximum number of table records in the client cache was permanently set at 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 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.

Feature	Description
Extras okay in Where clause	An X++ Select statement can have a Where clause that contains equality tests on all fields of any unique index, plus additional tests of other fields. The additional tests must be added with the && operator, not with \|\|. Such additional tests no longer prevent the caching mechanisms from improving performance. The following X++ select statement is an example of such a join. select * from tabCustTable where tabCustTable.AccountNum == "4000" && tabCustTable.CustGroup == "Gold";
join is supported	An X++ Select statement that joins tables can now be included in the caching processes. The Where clause must contain an equality test on a caching index. The following X++ select statement is an example of such a join. select from tabCustTable join tabCustGroup where tabCustTable.AccountNum == "4000" && tabCustTable.CustGroup == tabCustGroup.CustGroup; The preceding Select Join would cause the following Select to be serviced by the cache: select from tabCustGroup where tabCustGroup.CustGroup == "Gold";
Cross-company queries	Queries that use the crossCompany keyword of X++ are supported for caching when there is only a single company involved.
container field type	The container field type is now supported for caching, when the container field has a unique index based on it.
Table inheritance	Data queries against tables that support table inheritance are serviceable by the cache.
Valid Time State tables	Valid time state tables are supported by the caching mechanisms.

CacheLookup Property

The CacheLookup property of the table 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 the 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 on the CustTable in the Microsoft Dynamics AX application. It is 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 select forUpdate 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 select forUpdate 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. For more information, see Set-based Caching.

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.

Note

More code examples for caching can be found in chapter 12 of Inside Microsoft Dynamics AX 2009, ISBN: 978-0-7356-2645-4.

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.

Share via