Dynamics AX Table Caching with Examples Part 1 – Basics, Found, Found and Empty

In this blog series we will have a deeper look into the Dynamics AX table caching system, which is an essential for writing Dynamics AX solutions that perform well and generally overlooked by programmers. We will have look at that feature step by step and test it with some code examples.

Basically, caching is done by AX on both AOS and client side to improve performance by reducing the amount of data transferred between SQL database and AOS-Client. Caching setup for AX can be found on the ‘System Administration>Server configuration’ form (Same place you set the batch settings), and on the table level using the ‘CacheLookup’ property of the individual table :

In AX performance workshops they recommend aggressively caching the tables you create for a better performance, except the tables that receive too many updates or being changed simultaneously by different users.

Server configuration

The cache limits are set per AOS, first as the server configuration as number of records to be cached on AOS level. The default value is 2000 records per table group. The client cache is a factor of those values per table group, which is by default a division of 20 (100 records) per table group. Here we see another setting called ‘Entire table cache size’, remember this option which we will mention in detail in Entire table caching method. The client cache is flushed when you exit the client and repopulated from the server cache if you call the same record again. All caching in AX is done per company.

Table configuration

The table caching is done two ways, key based caching and set based caching. Basically key based means a record buffer is cached in the system indexed with its primary key whenever AX calls a select statement to fetch that record, and set based means entire table rows are cached on the server side. We will do examples for both and see how it works.

Now let’s set up an example table like below and try different forms of caching in it:

CacheLookup : Found

This is the most basic caching method of the key based caching methods and caches the record buffer using its primary key value as index in the caching system whenever a select statement is executed in AX and returned a nonempty record buffer. The cache is being indexed with its primary key setup in the table properties and not with its recid, but that does not mean you will not get a cached  record buffer when you use recid index or another one in your where clause. Now lets fill our table example with records (numbers from 1 to 1000 as Idx field and “num “+number in value field), set its CacheLookup value to ‘Found’ and run a test job. To control the level of caching, we use wasCached() method of the table buffer, which returns us an “CachedHow” enum value to state the record is cached on the client or in the server tier:

for(i=1;i<=1000;i++)</pre>
{

example.clear();

example.Idx=i;

example.value='num '+int2str(i);

example.insert();

}

static  void tableCachingTest(Args _args)</pre>
{

CachedTableExample example;

int i;

;

select example where example.Idx == 50;

info(strFmt("Test step 1 : %1", example.wasCached()));

//Second select comes from cache

select example where example.Idx == 50;

info(strFmt("Test step 2 : %1", example.wasCached()));

 

//Recid select

select example where example.Recid == 5637145628;

info(strFmt("Test step 3 : %1", example.wasCached()));

 

//Forupdate select

select forupdate example where example.Recid == 5637145628;

info(strFmt("Test step 3 : %1", example.wasCached()));

}

As you see the first select fetches data from the DB for the primary key ‘50’and subsequent selects use this cached data buffer instead of making another call to the database. The return value of ‘RecordCached’ means it is coming from the client cache of AX, the server tier cache returns “SrvRecordCached” value instead when you call the method on a cached record buffer. I intentionally duplicated the step 3 to show you that the record buffer also comes from the cache and not from the database when you select the table with forupdate keyword. In some sources on the internet it is being told that forupdate clause never fetches the record from cache but from the database instead. For ‘Found’ caching method that is not true. Here on the step 3 you can also try using the ‘value’ field, which will not make a difference and record will again come from the cache. If you try to fetch here another primary key, like ‘51’ it will similarly fetch it from the DB first and subsequent select calls for that record will be done from the cache.

Found caching is recommend for Group, Main and Reference type of tables in AX, but of course you are free to use it for any table that suits.

 CacheLookup : Found and Empty

This method is exactly the same as method ‘Found’ with one difference that it also caches the select calls that return empty values. This is useful for tables that system makes too many ‘exists’ checks, like discount table for example. Let’s test it using another similar test job, but this time also making calls for non existing records . First we set our table cachelookup property to “Found and empty” which will also flush the previous cache from the system.

static  void tableCachingTest(Args _args)

{

CachedTableExample example;

int i;

;

select example where example.Idx == 50;

info(strFmt("Test step 1 : %1", example.wasCached()));

//Second select comes from cache

select example where example.Idx == 50;

info(strFmt("Test step 2 : %1", example.wasCached()));

//Empty select

select example where example.Idx == 1050;

info(strFmt("Test step 3 : %1", example.wasCached()));

//Empty select second

select example where example.Idx == 1050;

info(strFmt("Test step 4 : %1", example.wasCached()));

//Another field empty select

select forupdate example where example.value == 'doesnotexists';

info(strFmt("Test step 5 : %1", example.wasCached()));

//Another field empty select second time

select forupdate example where example.value == 'doesnotexists';

info(strFmt("Test step 6 : %1", example.wasCached()));

}

As you see in this example, it caches the selects that return a table buffer as well as select statements that does not return a record. In the steps 5 and 6 I have also shown that the caching is done no matter you use primary key fields in your select statement or not, it registers your select calls with fields you use inside your where clause and caches them as not returning any buffer. Afterwards, all subsequent calls with the same where statement returns from the cache.

Buffer.disableCache() method

You can disable any record cache and force the record to be selected from the database by calling .disableCache(true) before selecting the table buffer. If you modify the previous example by calling example.disableCache(true), then you will see that subsequent select calls will be returned directly from the database.

[adinserter block=”9″]