Wednesday, 18 January 2017

Cache Lookup Property of table in ax 2012

Cache Lookup - Ax 2012

Caches are used on both the client and the server. It increases the performance, the ax will get data from the cache instead of doing round trips and DB calls. So for each table, it's good to use cache lookup property. Microsoft Dynamics Ax runtime manages the cache by removing old records when new records are added to the cache.

Client 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 can be maintained in a client cache is 100 records per table for the selected company.

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 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
None
No data is cached or retrieved from the cache for this table. This property value should be used for tables that are heavily updated or where it's unacceptable to read outdated data.

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 cahce 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.

Below is a list which shows to use the different type of cache lookup property as per table group.

Table GroupCache Lookup
Miscellaneous* See notes below
 Parameter EntireTable
 Group Found
 Main Found
 Transaction NotInTTS
 WorksheetHeader NotInTTS
 WorksheetLine NotInTTS
 Framework N/A
 Reference Found
 Worksheet NotInTTS
 TransactionHeader NotInTTS
 TransactionLine NotInTTS


Found
All successful caching key 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.
This is typically used for static (lookup) tables, such as Unit, where the record usually exists.

NotInTTS
All successful caching key 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 the database and subsequently from the cache. The record is select-locked when reading in a transaction, which ensures that the record cached is not updated while the transaction is active.
A typical example of the NotInTTS property is on the CustTable in the Microsoft Dynamics AX application. It is acceptable to read outdated data from the cache outside a transaction, but when data is used for validation or creating references, it is ensured that the data is real-time.

FoundAndEmpty 
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.
An example of FoundAndEmpty record caching is in the Discount table in the Microsoft Dynamics AX standard application. By default, the Discount table has no records. By using a FoundAndEmpty cache on this table, the keys that are queried for but not found are stored in the cache. Subsequent queries for these same non-existent records can be answered from the cache without a round trip to the database. 

10 comments:

  1. Hi Gautam,

    Could give me some practical example, because theoretically i'm understanding the concept But HOW & WHERE it is implemented in Practically Real-time.

    Note: you can provide an example with Your own table.

    Thanks & Regards,
    Johnkrish

    ReplyDelete
    Replies
    1. Hi Johnkrish, I'd suggest you to see table's cache lookup property in different tables of MSD AX. You will notice the difference based on how tables are being used.

      Delete
  2. Hi Gautam,

    Can you please give a real time example ?

    ReplyDelete
  3. You just copied from MS docs and pasted here, it would be good if you can write blog with real-time example or don't at all

    ReplyDelete
  4. I like the list with the table groups. But for Parameter tables the design pattern recommends "Found".
    https://docs.microsoft.com/en-us/dynamicsax-2012/developer/parameter-system-design-pattern

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. I just found out that the Cache Lookup property for CustTable is Found and not NotInTTS as mentioned here. Could there be a better explanation for this to help me understand?

    ReplyDelete
  7. OKAY LETSGO

    ReplyDelete
  8. Great and I have a swell supply: kitchen cabinet refacers

    ReplyDelete