Lookup Transformation(2) 优化



Tuning Lookup Transformation - Informatica

Q) How to tune lookup transformation to improve the performance of the mapping?

This is a frequently asked question in informatica interview. Follow the below steps to tune a lookup transformation:

1、缓存lookup
Cache the lookup transformation: This will query the lookup source once and stores the data in the cache. Whenever a row enters the lookup, the lookup retrieves the data from the lookup source rather than querying the lookup source again. This will improve the performance of lookup a lot.

2、限制Order by的列数（指定需要排序的列）
Restrict Order by columns: By default, the integration orders by on all ports in the lookup transformation. Override this default order by clause to include few ports in the lookup.

3、使用永久缓存（如果查找的数据源是不变的）
Persistent Cache: If your lookup source is not going change at all (example: countries, zip codes). Use persistent cache in this case.

4、使用静态缓存替换动态缓存（如果使用动态缓存时，更新缓存导致过度开销，避免使用动态缓存）
Prefer Static Cache over Dynamic Cache: If you use dynamic cache, the lookup may update the cache. Updating the lookup cache is overhead. Avoid dynamic cache.

5、限制lookup的端口
Restrict Number of lookup ports: Make sure that you include only the required ports in the lookup transformation. Unnecessary ports in the lookup make the lookup to take time in querying the lookup source, building the lookup cache.

6、在平面文件中使用lookup最好先排序
Sort the flat file lookups: If the lookup source is a flat file, using the sorted input option improves the performance.

7、给where语句中的列创建索引
Indexing the columns: If you have used any columns in the where clause, creating any index (in case of relational lookups) on these columns improves the performance of querying the lookup source.

8、数据库优化
Database level tuning: For relational lookups you can improve the performance by doing some tuning at database level