If a mapping contains Lookup transformations, you might want to enable lookup caching. When you enable caching, the Integration Service caches the lookup table and queries the lookup cache during the session. When this option is not enabled, the Integration Service queries the lookup table on a row-by-row basis.
The result of the Lookup query and processing is the same, whether or not you cache the lookup table. However, using a lookup cache can increase session performance for smaller lookup tables. In general, you want to cache lookup tables that need less than 300 MB.
Complete the following tasks to further enhance performance for Lookup transformations:
Use the appropriate cache type.
Enable concurrent caches.
Optimize Lookup condition matching.
Reduce the number of cached rows.
Override the ORDER BY statement.
Use a machine with more memory.