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 mapping performance for smaller lookup tables. In general, you want to cache lookup tables that need less than 300 MB.

To improve lookup performance on a logical data object, you can enable data object caching on the Data Integration Service. When you enable data object caching, the Data Integration Service caches the logical data object. To enable data object caching, you must deploy the mapping to an application, enable caching of the logical data object, and run the mapping with the command

infacmd ms runmapping

. When you run the mapping, the Data Integration Service queries the data object cache instead of the logical data object.

If you run the mapping from the Developer tool, the Lookup transformation queries the logical data object on a row-by-row basis.

When the Data Integration Service processes mappings that contain Lookup transformations, the Data Integration Service builds a cache in memory when it processes the first row of data in a cached Lookup transformation. If there are multiple Lookup transformations in a mapping, the Data Integration Service creates the caches sequentially when the first row of data is processed by the Lookup transformation. This slows Lookup transformation processing.

You can enable concurrent caches to improve performance. When the number of additional concurrent pipelines is set to one or more, the Data Integration Service builds caches concurrently rather than sequentially. Performance improves greatly when the mappings contain a number of active transformations that may take time to complete, such as Aggregator, Joiner, or Sorter transformations. When you enable multiple concurrent pipelines, the Data Integration Service no longer waits for active mappings to complete before it builds the cache. Other Lookup transformations in the pipeline also build caches concurrently.

When the Lookup transformation matches lookup cache data with the lookup condition, it sorts and orders the data to determine the first matching value and the last matching value. You can configure the transformation to return any value that matches the lookup condition. When you configure the Lookup transformation to return any matching value, the transformation returns the first value that matches the lookup condition. It does not index all ports as it does when you configure the transformation to return the first matching value or the last matching value.

When you use any matching value, performance can improve because the transformation does not index on all ports, which can slow performance.

By default, the Data Integration Service generates an ORDER BY statement for a cached lookup. The ORDER BY statement contains all lookup ports. To increase performance, suppress the default ORDER BY statement and enter an override ORDER BY with fewer columns.

The Data Integration Service always generates an ORDER BY statement, even if you enter one in the override. Place two dashes ‘--’ after the ORDER BY override to suppress the generated ORDER BY statement.

For example, a Lookup transformation uses the following lookup condition:

ITEM_ID = IN_ITEM_ID
PRICE <= IN_PRICE

The Lookup transformation includes three lookup ports used in the mapping, ITEM_ID, ITEM_NAME, and PRICE. When you enter the ORDER BY statement, enter the columns in the same order as the ports in the lookup condition. You must also enclose all database reserved words in quotes.

Enter the following lookup query in the lookup SQL override:

SELECT ITEMS_DIM.ITEM_NAME, ITEMS_DIM.PRICE, ITEMS_DIM.ITEM_ID FROM ITEMS_DIM ORDER BY
ITEMS_DIM.ITEM_ID, ITEMS_DIM.PRICE --

If you include more than one lookup condition, place the conditions in the following order to optimize lookup performance:

Equal to (=)
Less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=)
Not equal to (!=)

The Data Integration Service needs to query, sort, and compare values in the lookup condition columns. The index needs to include every column used in a lookup condition.

You can improve performance for the following types of lookups:

Cached lookups. To improve performance, index the columns in the lookup ORDER BY statement. The mapping log file contains the ORDER BY statement.
Uncached lookups. To improve performance, index the columns in the lookup condition. The Data Integration Service issues a SELECT statement for each row that passes into the Lookup transformation.