Dynamics AX Hints: November 2016

29 November 2016

How unique index join cache works

Unique index join caching is supported in AX 2012, however the description in msdn is not very clear in my opinion. Let's run several tests to find out the truth.

Problem description
Analyse how unique index join is cached by tracing T-SQL statements sent to MS SQL Server and using wasCached method.

Hints
Use the following code and trace RPC:Completed events in SQL Server Profiler.

static void TestJoinCache(Args _args)
{
    CustTable   custTable;
    CustGroup   custGroup;

    select AccountNum, PaymMode from custTable
        join custGroup, PaymTermId from custGroup
        where custTable.AccountNum == "US-004"
           && custGroup.CustGroup  == custTable.CustGroup;

    info(strFmt("CustTable from %1, CustGroup from %2",
        custTable.wasCached(), custGroup.wasCached()));
}