Consider a mapping with a cached lookup.When the session is running an index and data cache files are created for the lookup, depending on the session memory setting, a part of the cache is maintained in memory and the rest is written to disk as a file. For each row from the source qualifier, when the Lookup transformation is invoked, the index and data cache need to be processed to find the output data. If the relevant cache information for each processed row is not in memory then it has to be read from disk files into memory. This is called disk swapping. Frequent disk swapping can negatively impact performance.
For effective searching, the lookup cache data is sorted by condition ports sequence. So, if cache data for a particular key is in memory, then most probably the data for the adjacent keys will also be in memory. In order to take advantage of this, we can select the rows to be processed from the source qualifier in the same sorted order as the lookup condition ports. This increases the chance of getting hits for the required cache data in the memory and reduces disk swapping.
For successful implementation of this strategy, it is necessary to have database indexes on the source table (associated with Source Qualifier) matching the lookup condition columns.
What can we do to improve this information (2000 or fewer characters)