Источник:
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