A client-side cache is used only by the client when a select is executed from the client tier. If the record doesn’t exist in the client cache, the client will search the record in the server cache. If the same record doesn’t exist in the sever cache, it is retrieved from the database.
A server-side cache can be used by any connection to the server and is used when a select is executed on the server tier. If the record doesn’t exist in the server-side cache, the record is retrieved from the database.
We can configure the number of records to be maintained in the cache by using this path:
System administration > Area page > Setup > System > Server configuration
Cache lookup property of table
AOT > Data Dictionary > Tables > [TableName] > Properties > CacheLookup
Two types of table caching:
- Set based caching
- At design time, by setting the table’s CacheLookup property to EntireTable.
- In code, by using the RecordViewCache class.
- Single record caching
- At design time, by setting the table’s CacheLookup property to NotInTTS, Found or FoundAndEmpty.
- In code, by using disableCache method called with a parameter of false.
EntireTable CacheLookup property
All the records in the table are placed in the server cache after the first select. The SELECT statement WHERE clause must include equality tests on all fields of the primary index. An EntireTable cache is created for each table for a given company and is flushed whenever an insert, update, or delete is made to the table and every 24 hours by the AOS. It’s not recommended to use EntireTable property for table that’s frequently updated.
The cache created by the RecordViewCache class stores records in a linked list. We can search the cache sequentially for records that match the search criteria. If the SELECT statement contains an ORDER BY clause, a temporary index is placed on the cache and the runtime uses the index when searching records.
NotInTTS CacheLookup property
All successful selects are cached. When in a transaction (after ttsBegin), no caches made outside the transaction are used. When inside a transaction, the record is read once from database and subsequently from the cache. The record is select-locked when read in a transaction, which ensures that the record cached is not updated while the transaction is active.
Found CacheLookup property
All successful selects are cached. All caching key selects are returned from the cache if the record exists there. A select forUpdate in a transaction forces reading from the database and replaces the record in the cache. Usually this type is used for static (lookup) tables, such as Unit, where the record usually exists.
FoundAndEmpty CacheLookup property
All selects on caching keys are cached, including selects that are not returning data. All caching key selects are returned from caching if the record exists there, or the record is marked as nonexistent in the cache. A select forUpdate in a transaction forces reading from the database and replaces the record in the cache.
Code Example to demonstrate performance:
Set the appropriate table group property depending on how the table is used.
Below is a list which shows to use the different type of cache lookup property as per table group.