Dynamics AX Table Caching with Examples Part 2 – Not in TTS and Entire Table cache

In this part of the series, we will look deeper into the table caching methods of Not in TTS and Entire table.

CacheLookup : Not in TTS

Now let’s have a look at the most confusing method of caching in AX, the NotInTTS method. This method is very similar to Found caching but if you select your cached record inside a tts block, it rereads it from the database to ensure that the data you select in tts block is the latest version of that record. It is a bit hard to get the usage of it but the example below will make it clear :

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()));

ttsBegin;

//tts select

select example where example.Idx == 50;

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

//tts select second

select example where example.Idx == 50;

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

//tts and forupdate select

select forUpdate example where example.Idx == 50;

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

ttsCommit;

//Outside tts select comes from cache

select example where example.Idx == 50;

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

ttsBegin;

//Again in tts

select example where example.Idx == 50;

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

ttsCommit;

}

As you see the first block of code works same as Found method. When in step 3 we enter into a tts block and call select again, it does not come from the cache but read from the database. In step 4 which is inside the same tts block, the record comes from cache again. The idea of NotinTTS caching is to ensure that when you update the record you selected, it has the latest field values from the database and does not create a conflict with other users updated it before. It is mainly used by Transaction type of tables in AX which there is a risk of creating inconsistent data while two different users trying to update the same record.
I have added another step to show you how it behaves with the forupdate keyword. Unlike the ‘Found’ method, in ‘NotinTTS’ method all select calls with forupdate are fetched from the database and not from the cache.
Outside the tts block, as you see, the record again comes from the cache until we open another tts block and call the select again in step 7 which does not come from the cache as expected.

You need to use this method carefully and make it a best practice for yourself to always select the record buffer from within the tts scope if you want it to be up to date and not coming from the cache.  If you select the record buffer from outside the tts block and use it inside the tts, it normally comes from the cache and not the most up to date version of the record :

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()));

ttsBegin;

//same record buffer used in tts scope

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

ttsCommit;

}

CacheLookup : Entire table

This is the method that caches entire table rows into the server memory (or disk depending on the cache size) and reads the data from there instead of making new database calls. You should use entire table caching wisely because you may seriously harm system performance if you use it on incorrect table types. Microsoft recommends using this caching method on Parameter type of tables which has only one record per company but it is also fair to use it some reference tables that has 3-5 rows on them which does not get so many updates from the users.
Entire table basically caches entire of the table rows when you call a select statement for that table for the first time, and stores them, per company, in an instance of RecordViewCache class in the AOS which will then be shared among the clients that are connected to the AOS. If you update or delete a record from that table, the cache is flushed and recreated on your next select statement. Now let’s create a test job and see how it works setting our table CacheLookup property to ‘EntireTable’ :

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()));

ttsBegin;

//Forupdate select

select forUpdate example where example.Idx == 50;

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

example.value = 'num updt 50';

example.update();

ttsCommit;

//Third select after update block

select example where example.Idx == 50;

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

//Fourth select after update

select example where example.Idx == 50;

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

Dictionary::dataFlush(example.TableId);

//Fifth select after data flush

select example where example.Idx == 50;

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

}

Here you see that when you call your first select statement, it builds a full table cache in the server first and returns the value from that cache. Afterwards this record is cached in the client and comes from the client in step 2. A good detail on Entire table cache is,  it never uses a cached record when you select the record with forupdate keyword, to ensure the record is up to date before you run an update on it. After you update your record the cache on the server and client is flushed and when you run your select statement the cache is again regenerated and your record comes from the server. Step 5 shows that the record comes again from the client cache.
I added another step for the data flush method (which we will dive into detail in part 3 of the blog) to show you that Entire record caching works differently on server and client tiers. On step 6 the record again comes from the server cache but this time the entire cache does not get rebuilt since the dataFlush() method only flushes the cache of the calling tier, in this case: the client. In short, Entire Table method is cached as entire recordset in the server tier and behaves somehow like “Found and empty” for individual cached records on client tier (with exception of forupdate keyword). If you run a test job to test it :

static  void tableCachingTest(Args _args)

{

CachedTableExample example;

int i;

;

Dictionary::dataFlush(example.TableId);

select example where example.Idx == 51;

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

select example where example.Idx == 51;

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

select example where example.Idx == 52;

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

select example where example.Idx == 52;

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

select example where example.Idx == 1050;

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

select example where example.Idx == 1050;

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

ttsBegin;

select example where example.Idx == 51;

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

select example where example.Idx == 51;

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

ttsCommit;

}

You will see that records are cached individually on the client side including ones that does not return a value. I have added extra step to show that Entire Table caching does not care the tts block, like NotinTTS caching method and uses the client cached record in the block as normal behavior.

There are some points you need to take into account with Entire table caching method. First, when you try to to use bulk SQL operations of AX, like update_recordset and delete_from on tables that are cached with Entire Table method, it does not run as a bulk operation. Those calls will always run as row by row operations and you will not benefit the added performance of the bulk SQL operations as much as you do on other tables. Second one is, if you join an Entire Table cached record with another table which is not Entire table cached, the result will not come from the cache but from the database directly. To benefit the Entire table cache on your joins, both tables must be cached with Entire table caching.

Regardless of the situation, all entire table caches stored on the server memory is flushed every 24 hours and regenerated when a user calls a select statement to it. The cache sits in the server memory for faster access until the limit in the server configuration (mentioned in part 1) by Kbytes is reached per table. If this limit is exceeded, the cache is flushed to the disk, which seriously affects the performance of the cached selects.

RecordViewCache class

It is also possible to use RecordViewCache class on your own to create a record cache for yourself on the server. This is handy if you want to write a job that will use the same records multiple times and you do not want to use database calls or in memory temp table to do the job. You create an instance of this class by simply passing a buffer to it which is selected with nofetch keyword.  The code you write which uses RecordViewCache must run strictly on the server tier, otherwise you receive an error saying “The cursor is invalid for instantiating recordViewCache.”. This cached record then can be used in the same scope and flushed as soon as the class gets out of scope. In the following example we create a class with main method and run the method on the server to cache record 50. We check if the field values of the record is being cached and see that RecordViewCache cache only caches the buffer being passed on creating the instance:

server static void main(Args _args)
{
CachedTableExample example;
RecordViewCache recordViewCache;
;
select nofetch example where example.Idx==50;

// Cache it on server
recordViewCache = new RecordViewCache(example);

// Use cache.
select firstonly example where example.Idx==50;

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

select firstonly example where example.Idx==50;

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

select firstonly example where example.Idx==51;

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

[adinserter block=”9″]