[X++] EntireTableCache and RecordViewCache
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 Set-based
caching.
There are two ways to archive this caching:
- At design time using the table property CacheLookup with value
EntireTable
- In code by using the class
RecordViewCache
.
Class RecordViewCache
Since most of AX developers are familiar with the table property, this article should point out how to use the class RecordViewCache
.
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.