You can configure a Lookup transformation to cache the lookup source to increase lookup performance. Enable lookup caching when the lookup table or file is large.
The Integration Service builds a cache in memory when it processes the first row of data in a cached Lookup transformation.
The Integration Service creates caches as the source rows enter the Lookup transformation.
It allocates memory for the cache based on the amount you configure in the transformation or session properties. The Integration Service stores condition values in the index cache and output values in the data cache. The Integration Service queries the cache for each row that enters the transformation.
If the data does not fit in the memory cache, the Integration Service stores the overflow values in the cache files. The Integration Service also creates cache files in the specified cache directory. When the
session
mapping
completes, the Integration Service releases cache memory and deletes the cache files unless you configure the Lookup transformation to use a persistent cache.
If you use a flat file lookup
or pipeline lookup
, the Integration Service always caches the lookup source. If you configure a flat file lookup for sorted input, the Integration Service cannot cache the lookup if the condition columns are not grouped. If the columns are grouped, but not sorted, the Integration Service processes the lookup as if you did not configure sorted input.
When you configure a lookup cache, you can configure the following cache settings:
Sequential and concurrent caches
You can configure the session to build caches sequentially or concurrently. When you build sequential caches, the Integration Service creates caches as the source rows enter the Lookup transformation. When you configure the session to build concurrent caches, the Integration Service does not wait for the first row to enter the Lookup transformation before it creates caches. Instead, it builds multiple caches concurrently.
Persistent cache
You can save the lookup cache files and reuse them the next time the Integration Service processes a Lookup transformation configured to use the cache.
Recache from source
If the persistent cache is not synchronized with the lookup source, you can configure the Lookup transformation to rebuild the lookup cache.
You can configure the Lookup transformation to rebuild a persistent lookup cache.
Static cache
You can configure a static cache for any lookup source. By default, the Integration Service creates a static cache. It caches the lookup file or table and looks up values in the cache for each row that comes into the transformation. When the lookup condition is true, the Integration Service returns a value from the lookup cache. The Integration Service does not update the cache while it processes the Lookup transformation.
Dynamic cache
To cache a lookup source and update the cache, configure a Lookup transformation with dynamic cache. The Integration Service dynamically inserts or updates data in the lookup cache and passes the data to the target. The dynamic cache is synchronized with the target.
Shared cache
You can share the lookup cache between multiple transformations. You can share an unnamed cache between transformations in the same mapping. You can share a named cache between transformations in the same or different mappings. Lookup transformations can share unnamed static caches within the same target load order group if the cache sharing rules match. Lookup transformations cannot share dynamic cache within the same target load order group.
You can share the lookup cache between multiple transformations in the same mapping.
When you do not configure the Lookup transformation for caching, the Integration Service queries the lookup source for each input row. Whether or not you cache the lookup source, the result of the Lookup query and processing is the same. However, you can increase lookup performance on a large lookup source if you enable lookup caching.
ASK INFAPreview
