Site icon DAXRunBase

Find tables without cacheLookup property set

During the cleanup exercise of our code upgrade to Dynamics 365 Finance and Operations we have noticed that many of our custom tables did not have the cacheLookup configured. It can have a negative performance impact on your system, so it is best practice to have this properly configured. You can find below a job to find tables without cacheLookup property set.

You can find more details about caching in the documentation:

https://docs.microsoft.com/en-us/dynamicsax-2012/developer/single-record-caching

It is going to create a new project, with all the tables which were created on the CUS layer, that you need to verify.

static void WIK_findTablesWithoutCache(Args _args)
{
    #AOT
    #AOTExport
    #AviFiles
 
    ProjectName             projectName = "TableCache";
    TreeNodeIterator        tni     = TreeNode::findNode(#TablesPath).AOTiterator();
    TreeNode                node;
    ProjectNode             projectNode;
    SysDictTable            dictTable;
    Common                  common;
    int                     cnt;
    int64                   recordCount;
    int64                   counter;
    SysOperationProgress    pr = SysOperationProgress::newGeneral(#AviUpdate, 'Processing treenode', 0);
 
    void addTreeNodeToProject(TreeNode _node)
    {
        TmpIdRef        tmpIdRef;
 
        tmpIdRef.clear();
        tmpIdRef.Name = SysTreeNode::getPath(_node);
        tmpIdRef.Mode = SysTreeNode::path2ApplObjectType(tmpIdRef.Name);
        tmpIdRef.useMode = UtilFileType::Application;
        tmpIdRef.insert();
 
        SysProjectFilterRunBase::addProjectNodes(tmpIdRef, projectNode);
    }
 
    projectNode    = infolog.projectRootNode();
    projectNode    = projectNode.AOTfindChild(#expProjectprivate);
    projectNode    = projectNode.AOTfindChild(projectName);
 
    if( !projectNode)
    {
        projectNode = SysTreeNode::createProject(projectName, ProjectSharedPrivate::Projprivate);
    }
 
    //projectNode.loadForInspection();
 
    tni = TreeNode::findNode(#TablesPath).AOTiterator();
    node = tni.next();
 
    while (node)
    {
        pr.incCount();
        pr.setText(node.AOTname());
 
        dictTable = SysDictTable::newTreeNode(node);
 
        if (!dictTable.isView() && !dictTable.isMap() && !dictTable.isTmp() && !dictTable.isTempDb() && !dictTable.isSystemTable()
            && dictTable.cacheLookup() == RecordCacheLevel::None
            && SysTreeNode::existsInLayer(dictTable.treeNode(), UtilEntryLevel::cus)
            && !SysTreeNode::existsInLayer(dictTable.treeNode(), UtilEntryLevel::sys)
            && !SysTreeNode::existsInLayer(dictTable.treeNode(), UtilEntryLevel::syp)
            )
        {
            counter++;
            //recordCount = dictTable.recordCount(true);
            //info(strFmt('%1 - %2 - %3', counter, dictTable.name(), recordCount));
            common = dictTable.makeRecord();
            select firstOnly RecId from common;
            if (common.RecId)
            {
                addTreeNodeToProject(node);
            }
        }
 
        node = tni.next();
    }
 
    //projectNode.treeNodeRelease();
    //projectNode.AOTrefresh();
}
Exit mobile version