How to improve performance of a lookup by using “Pre-build Lookup Cache” option?

By default Informatica Integration service starts building the lookup cache only when the first input row is received by the lookup transformation during the session execution. This may sometimes cause the session to wait until the cache is build especially for large lookup files or complex lookup queries.

In order to start building the lookup cache as soon as the session starts you can:-

  • configure the lookup property “Pre-build Lookup Cache
  • at mapping or session level
  • Integration service uses session level setting if you configure this option as Auto at the mapping level

There are three options you can set for this property:-

  1. Auto – Integration Service uses the session level setting
  2. Always Allowed – Integration Service creates an additional pipeline to build the cache even before the first row is received by the lookup transformation.
  3. Always Disallowed – Integration Service cannot pre-build the cache.

In order for this property to take effect you must configure the session property – “Additional Concurrent Pipelines for Lookup Cache Creation”

  • This property configures the number of pipelines the Integration service can build concurrently.
  • Its value should be greater than zero for the pre-build cache option to take effect.

I hope this helps you improve your session performance. Leave a comment if you need any more details on this property.

Join the Conversation

1 Comment

Leave a comment

Your email address will not be published. Required fields are marked *