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;

}

screen21

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;

}

screen22

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

}

screen23

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;

}

screen24

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.

screen25

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

screen7

 

[adinserter block=”9″]

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 :

screen1screen2

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:

capturemh5

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

}

screen4

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

}

screen5

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

}

screen6

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″]

 

.NET goes open source and multi platform with the new framework

Remember those old days of Windows vs. Linux battles? Linux being a free and open source operating system held the biggest market share on server systems whereas Windows was the chosen operating system due to its simplicity by desktop users. Developments in the mobile world changed that game and as the popularity of smart phones and tablets increased, Unix based Apple IOS and Linux based Google Android became most popular mobile operating systems.
In April 2011, Microsoft announced that they will open up the source code of Windows, which shocked whole IT community.

clip_image002-custom

Continue reading “.NET goes open source and multi platform with the new framework”