The EntireTable cache decision helper

My first serious post is about table caching. You know everything about it? Read on anyway!

The CacheLookup property

I hope you know about the property CacheLookup that exists on table level. It can be set to the values

  • None
  • NotInTTS
  • Found
  • FoundAndEmpty
  • EntireTable

This is well-documented in the MSDN article Single-record Caching [AX 2012]. I would like to focus on EntireTable today.

The issue

In MSDN the EntireTable cache is made more concrete in article Set-based Caching [AX 2012] where you can find the important note

Avoid using EntireTable caches for large tables because once the cache size reaches 128 KB the cache is moved from memory to disk. A disk search is much slower than an in-memory search.

Unfortunately one little thing in there is not correct! Actually you can set up the size of the cache (see next passage).

You should be careful with using the EntireTable cache because you want to gain performance using it but you might cause a performance issue at the same time. It is very costly when the cache is swapped to the disk!

Since the usage of EntireTable cache is very useful in the right situations: What are the right situations? When do I reach the margin of nnn KB for table X?
It definitely would help to know how many records a table can have before swapping is done by the framework. That’s what I can help you with!

Setup of EntireTable cache size

Server configuration
The value for the cache size can be set in the Server configuration form you can find in System administration > Setup > System. Obviously the size can be set up differently between the AOSses you have. That does not make too much sense in my opinion. You’d need to align to the lowest value of them which leads to all the other AOSses having unnecessary high settings.

The default value for AX 2012 R3 is 96 KB.

The math

…is quite easy. Each record of a table uses a certain amount of space in memory – depending on what types of and how many fields are included in it. If you have a table with records of 1KB you can have a maximum of 96 records in it until the EntireTable cache gets swapped to disk, for example.

The tool

As a developer you want something integrated to your development environment. Let’s do it and integrate the EntireTable cache decision helper to the context menu in the AOT. How this is done is documented in MSDN, too (How to: Add Items to the AOT Add-Ins Menu [AX 2012]). All we need for this is a small class, an action menu item and two tiny customizations.

Class XltyCacheDecisionHelper

class XltyCacheDecisionHelper extends RunBase
{
}
 
public static client server ClassDescription description()
{
    return 'CacheLookup analysis';
}
 
public static void main(Args args)
{
    SysContextMenu sysContextMenu;
    TreeNode treeNode;
    TableId tableId;
    if (SysContextMenu::startedFrom(args))
    {
        sysContextMenu = args.parmObject();
        treeNode = sysContextMenu.first();
        if (treeNode)
        {
            tableId = SysTableBrowser::treeNode2TableId(treeNode);
        }
    }
    else
    {
        tableId = pickTable();
    }
    if (tableId)
    {
        XltyCacheDecisionHelper::showEntireTableCacheAnalysis(tableId);
    }
}
 
private static void showEntireTableCacheAnalysis(TableId _tableId)
{
    SysDictTable sysDictTable = new SysDictTable(_tableId);
    real recordSize = sysDictTable.recordSize();
    real maxBytesForEntireTableCache = 1024 * 96; // default server setting is 96kb per table
    int maxRecordsEntireTableCache = real2int(maxBytesForEntireTableCache / recordSize); // precision loss is wanted here - cut decimals
    info(strFmt('Table %1 record size is %2 bytes -> %3 is maximum record count for EntireTable caching without swapping!', sysDictTable.name(), recordSize, maxRecordsEntireTableCache));
}

Action menu item
Create an action menu item named XltyCacheDecisionHelper pointing to the class with the same name.

Customize class SysContextMenu, method verifyItem
Search for the part in the switch case that handles the case MenuItemType::Action and add a new case for the menuItem switch

    case menuitemActionStr(XltyCacheDecisionHelper):
        isValid = _firstType == UtilElementType::Table && !firstNode.AOTIsOld();
        break;

Customize menu SysContextMenu

CacheLookup analysis

Add the action menu item XltyCacheDecisionHelper to the menu. You might want to place it somewhere below Table browser.

That’s it! The information you need to be able to better decide whether you should use EntireTable cache is available from the AOT now!

Addendum

The functionality assumes that the minimum setting of the Entire table cache size is 96. To make it even better you could loop the server configurations and use the lowest of the values.
PLUS: No warranty and please do not use this in productive environments. This wouldn’t make any sense because it’s a developer’s tool but you never know 🙂

Update

In the meantime I released a downloadable version of it. Come this way!

2 thoughts on “The EntireTable cache decision helper

Leave a Comment