26 December 2016

How EntireTable cache works in AX2012 R3

The description of EntireTable cache on msdn seems ambiguous. On the one hand, all the records in the table are placed in the cache after the first select. On the other hand, the SELECT statement WHERE clause must include equality tests on all fields of the unique index. Let's run several tests to analyse it.

Problem description
Analyse how EntireTable cache works by tracing T-SQL statements and using wasCached method.

Hints
Run select statements with and without where clause on server and client.
Solution
The settings from the previous post can be used to trace T-SQL statements.
The following code sample can be used for tests:
CustGroup   custGroup;

select CustGroup from custGroup
    where custGroup.CustGroup == "20";

info(strFmt("CustGroup from %1", custGroup.wasCached()));
I run series of select statements on server and client side (AOS was restarted before each series) and aggregated the results into 2 tables.

Table 1. Tests with where clause
Test
X++
T-SQL
Server
Client
1
select CustGroup from custGroup
where custGroup.CustGroup == "20";
Yes
TableCached
SrvTableCached
2
select CustGroup from custGroup
where custGroup.CustGroup == "20";
No
RecordCached
RecordCached
3
select CustGroup from custGroup
where custGroup.CustGroup == "20";
No
RecordCached
RecordCached
4
select CustGroup from custGroup
where custGroup.CustGroup == "40";
No
TableCached
SrvTableCached
5
select CustGroup from custGroup
where custGroup.CustGroup == "40";
No
RecordCached
RecordCached

Table 2. Tests without where clause
Test
X++
T-SQL
Server
Client
1
select CustGroup from custGroup;
Yes
TableCached
SrvTableCached
2
select CustGroup from custGroup;
No
TableCached
SrvTableCached
3
select CustGroup from custGroup
where custGroup.CustGroup == "40";
No
TableCached
SrvTableCached
4
select CustGroup from custGroup
where custGroup.CustGroup == "40";
No
RecordCached
RecordCached

Comments:
- T-SQL statement was sent to MS SQL Server only once, all fields and all records were selected:
SELECT T1.CUSTGROUP,T1.NAME,T1.CLEARINGPERIOD,
... (all fields are selected)
FROM  CUSTGROUP T1
WHERE ((T1.PARTITION=@P1) AND (T1.DATAAREAID=@P2)) 
- with or without where clause the whole table was cached after the first select statement
- the subsequent call with where clause changed cache value to RecordCached.

Conclusions
The following conclusions are relevant for a table with EntireTable in CacheLookup property.
1. A table is fully cached per company after the first select statement, regardless of where clause.
2. All fields are fetched into a table buffer, regardless of a field list in X++ statement.
3. If a select statement contains equal-to predicates in where clause that exactly match all fields of a unique index, caching may follow the rules of Found caching and wasCached method may return RecordCached.
4. RecordCached returned value of wasCached method represents scope of a tier the method is called on.

No comments:

Post a Comment