Configure the Lookup cache modes

Cache Mode specify the caching of data in lookup task. We can specify one of following three lookup mode for an Lookup task:

1.     Full cache

2.     Partial cache

3.     No cache

In full cache all data from loop table are loaded into local cache in advance. In Partial cache mode, lookup table is queried for every new value and the fetched record is cached for further use. In No cache mode lookup table is queried for each record and fetched record from lookup table is not cached. 

Let us understand the hit and load statistics by an example:

Suppose we have two table Invoice and Customer. Invoice table has CustomerID that is foreign key, referencing to the CustomerID of Customer table. Suppose

Number of records in Invoice table = 100,000

Number of records in Customer table = 10,000

Count of distinct CustomerID in Invoice table = 2,000

Now following is the hit-load statistics in different cache modes:

Full cache:

Number of query hit to Customer table = 1

Number of records fetched from Customer table = 10,000

Partial cache:

Number of query hit to Customer table = 2,000

Number of records fetched from Customer table = 2,000

No cache:

Number of query hit to Customer table = 100,000

Number of records fetched from Customer table = 100,000