This is a often requested query in informatica interview. Follow the under steps to tune a lookup transformation:
Cache the lookup transformation: This will question the lookup supply as soon as and shops the info within the cache. Whenever a row enters the lookup, the lookup retrieves the info from the lookup supply slightly than querying the lookup supply once more. This will enhance the efficiency of lookup so much.
Restrict Order by columns: By default, the mixing orders by on all ports within the lookup transformation. Override this default order by clause to incorporate few ports within the lookup.
Persistent Cache: If your lookup supply shouldn’t be going change in any respect (instance: international locations, zip codes). Use persistent cache on this case.
Prefer Static Cache over Dynamic Cache: If you utilize dynamic cache, the lookup could replace the cache. Updating the lookup cache is overhead. Avoid dynamic cache.
Restrict Number of lookup ports: Make positive that you simply embody solely the required ports within the lookup transformation. Unnecessary ports within the lookup make the lookup to take time in querying the lookup supply, constructing the lookup cache.
Sort the flat file lookups: If the lookup supply is a flat file, utilizing the sorted enter possibility improves the efficiency.
Indexing the columns: If you may have used any columns within the the place clause, creating any index (in case of relational lookups) on these columns improves the efficiency of querying the lookup supply.
Database stage tuning: For relational lookups you possibly can enhance the efficiency by doing a little tuning at database stage.