dynamicsaxhints: How EntireTable cache works in AX2012 R3

[Blog bot]

Источник: http://dynamicsaxhints.blogspot.com/...ax2012-r3.html
==============

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.

Источник: http://dynamicsaxhints.blogspot.com/...ax2012-r3.html