The Best Practice standards of Microsoft says that a table should be cached as much as possible.
For small tables it's recommended to use the table property CacheLookup and set it to
EntireTable. When using
EnitreTableCache all records in the table will be cached on the server and the cache is shared across all connections to the AOS.
When a select statement is used on a table using
EntireTableCache on the client tier, it first searches it's own cache (client side) and then the server side.
Also keep in mind that the
EntireTableCache is generated for each table for each company.
That means if you have two selects on the same table for different companies the entire table is cached twice!
This caching is called
There are two ways to archive this caching:
- At design time using the table property CacheLookup with value
- In code by using the class
Since most of AX developers are familiar with the table property, this article should point out how to use the class
First we need to create a record buffer by using the keyword
nofetch in our first statement:
select noFetch firstOnly * from CustTable;
The result is that the query is prepared and ready to be executed but the actual data load is not performed at that point.
Short explanation of noFetch:
From this point you could traverse through the record with the
NEXT keyword. Since the
NEXT only refers to the first record in the
SELECT you could get the idea that it would give some problems when joining tables in the
SELECT. However, AX handles that as you would expect and returns the full data set.
Now we can pass the buffer to the
RecordViewCache class. The cache is created on the server.
It is only accessible by the process that creates the cache object!
All following select statements are issued against the cache now. Following an example:
CustTrans custTrans; RecordViewCache recordViewCache; // getting the record select firstOnly noFetch * from custTrans where custTrans.AccountNum == '450481'; // Caching the selected record recordViewCache = new RecordViewCache(custTrans); // retrieving the record from cache select firstOnly * from custTrans where custTrans.AccountNum == '450481' && custTrans.CurrencyCode == 'EUR';
I hope you get an idea of how to use the
RecordViewCache class now.