Dynamics AX Hints: 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()));
}

Solution
The following settings can be used in SQL Server Profiler

Client cache
1. Restart AOS.

2. Run TestJoinCache job for the first time.
T-SQL statement:

SELECT T1.ACCOUNTNUM,T1.INVOICEACCOUNT,T1.CUSTGROUP,
... (all fields are selected)
T2.CUSTGROUP,T2.NAME,T2.CLEARINGPERIOD,
... (all fields are selected)
FROM CUSTTABLE T1 CROSS JOIN CUSTGROUP T2
WHERE ((T1.PARTITION=@P1) AND (T1.DATAAREAID=@P2)) 
AND (((T2.PARTITION=@P3) AND (T2.DATAAREAID=@P4)) 
AND ((T1.ACCOUNTNUM=@P5) AND (T2.CUSTGROUP=T1.CUSTGROUP)))

CustTable has Found in CacheLookup, that's why all fields are fetched from the database, nevertheless only AccountNum and PaymMode are requested in X++ statement.
CustGroup has EntireTable in CacheLookup, that's why all fields are fetched from the database. I would expect all records of CustGroup table to be selected, but only one is in fact.
Infolog message:
"CustTable from NotCached, CustGroup from NotCached".
For the first time records are fetched from the database - it is logical.

3. Run the job for the second time.
T-SQL statement:
there are no calls.
Infolog message:
"CustTable from RecordCached, CustGroup from RecordCached"
Both records are fetched from client cache. Entire CustGroup table was not cached, that's why wasCached method returned RecordCached value.

Server cache
1. Create TestJoinCache class with such method:

public server static void serverCall()
{
    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()));
}

2. Create TestJoinCacheServer job to call the method on server:

static void TestJoinCacheServer(Args _args)
{
    TestJoinCache::serverCall();
}

3. Restart AOS.

4. Run the job for the first time.
T-SQL statement:

SELECT T1.ACCOUNTNUM,T1.INVOICEACCOUNT,T1.CUSTGROUP,
... (all fields are selected)
T2.CUSTGROUP,T2.NAME,T2.CLEARINGPERIOD,
... (all fields are selected)
FROM CUSTTABLE T1 CROSS JOIN CUSTGROUP T2
WHERE ((T1.PARTITION=@P1) AND (T1.DATAAREAID=@P2)) 
AND (((T2.PARTITION=@P3) AND (T2.DATAAREAID=@P4)) 
AND ((T1.ACCOUNTNUM=@P5) AND (T2.CUSTGROUP=T1.CUSTGROUP)))

The same as client cache.
Infolog message:
"CustTable from NotCached, CustGroup from NotCached".
The same as client cache.

5. Run the job for the second time.
T-SQL statement:

SELECT T1.CUSTGROUP,T1.NAME,T1.CLEARINGPERIOD,
... (all fields are selected)
FROM CUSTGROUP T1
WHERE ((PARTITION=@P1) AND (DATAAREAID=@P2))

CustTable record has been already cached. CustGroup is going to be cached, that's why all records in the current company are fetched from the database.
Infolog message:
"CustTable from RecordCached, CustGroup from TableCached"
CustTable and CustGroup are fetched from cache.

6. Run the job for the third time.
T-SQL statement:
there are no calls.
Infolog message:
"CustTable from RecordCached, CustGroup from TableCached"
Both records are fetched from cache.

Infolog messages look a bit strange to me. I expected values like: SrvRecordCached or SrvTableCached, but we can analyse it in an another post.

Conclusion
Unique index join is cached differently based on where a select statement is called for the first time - client or server side.
If the select statement is called on client, then both records are cached and next call will use cache to get the records.
If the select statement is called on server, then the second call can cache an entire table and following calls will use cache to get the records.

Dynamics AX Hints

29 November 2016

How unique index join cache works

No comments:

Post a Comment