Cache Lookup Property of table

In this article, I have discussed the cache lookup feature of the table.

Cache lookup is the table property and used for fast retrieval of data. Rather than fetching the data directly from database it fetches from the cache.

So for each table, it’s good to use the cache lookup property.

 Caches are of 2 types:-

1. client-side cache

2. server side cache

1. client-side cache:-

A Client-side cache can be used only by the client. The client cache is used when a select statement is executed from the client tier. If no records are found in the client cache, the client then searches in the server cache for the records. If the record isn’t located in the server cache, it will retrieve from the database. The maximum number of records that can be maintained in a client cache is 100 records per table for the selected company.

2. Server Cache

A server-side cache can be used by any connection to the server. The server cache is used when a select is executed on the server tier. If no record is found in the server cache, it will retrieve from the database. The maximum number of records maintained in a server cache is 2000 records for the selected company.

Types of Cache Lookup :-

None

EntireTable

Found

NotInTTS

FoundAndEmpty

Found:-

• Only successful selects (which have data) are cached.
• If a key select is found in the cache, it is fetched from the cache; if it's not found, it is fetched from the database and stored in the cache.
• It is used in static tables where data don't change.

FoundAndEmpty:-

• It stores all selects whether it has data or not.
• ForUpdate updates the data in the database and in cache as well (Only the specific data which is updated).

NotInTTs:-(transaction tracking system)

• It maintains the cache inside the transaction (ttsbegin).
• Outside caches are not used in the transaction.
• Select brings all records in the cache from the database and then future selects fetch the data from the cache in that transaction.
• The record is select-locked which ensures that the record cache will not be updated while a transaction is active.

EntireTable:-

• First, select brings the entire table (all records) into the cache and then other selects fetch data from the cache.
• On insertion, updation and deletion, the cache will be flushed and then select brings the entire table in the cache.
• (EX:-VendGroup Table)

None:-

• No cache is maintained.
• It is used when we have constant updation.