Cache Lookup Property of table

 1. Caches are used on both the client and the server. 

2. It increases the performance, the ax will get data from the cache instead of doing round trips and DB calls. 

 3. 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:-

1. Server (code)

2. Cache.

3. Database.

Let us say there are 1000 records in the database.

select firstonly 100  from a table. In the first stage, this statement will fetch records from the database and store them in the cache. Now in the cache, we have 100 records.

the cache will store a few records.

select first only 50 records from a table. This statement will fetch records from the cache because 100 records are present in the cache.

If we update the table, then it first executes it in the database and then updates the matching records in the cache also.

NotInTTs:-(transaction tracking system)

For every transaction which is creating, updating and deleting we need to use ttsbegin and ttscommit.

Let us take a scenario we have a record value is 10

we are updating the value in between the transactions from 10 to 20, this will update the value in the database and then in a cache.

EntireTable:-

Creates a set-based cache on the server. The entire table is cached as soon as at least one record is selected from the table. An EntireTable cache is flushed whenever an insert, update or delete is made to the table. So first select read all records from DB for the selected company and all the further selects will take data from the cache instead of DB calls.

None:-

All the scenarios are disabled.

FoundAndEmpty:-

if we are searching for a record which is not there in db

for first tym it will fetch from db and store the result in cache and from next tym it will shows the result from cache that the record is not there it will not go to db.