Performance by lookup

1)We use Lookup transformations that query the largest amounts of data to improve overall performance. By doing that we can reduce the number of lookups on the same table.

2)If a mapping contains Lookup transformations, we will enable lookup caching if this option is not enabled .
We will use a persistent cache to improve performance of the lookup whenever possible.
We will explore the possibility of using concurrent caches to improve session performance.
We will use the Lookup SQL Override option to add a WHERE clause to the default SQL statement if it is not defined
We will add ORDER BY clause in lookup SQL statement if there is no order by defined.
We will use SQL override to suppress the default ORDER BY statement and enter an override ORDER BY with fewer columns. Indexing the Lookup Table
We can improve performance for the following types of lookups:
For cached lookups, we will index the lookup table using the columns in the lookup ORDER BY statement.
For Un-cached lookups, we will Index the lookup table using the columns in the lookup where condition.

3)In some cases we use lookup instead of Joiner as lookup is faster than joiner in some cases when lookup contains the master data only.

4)This lookup helps in terms of performance tuning of the mappings also.

session performance

Session performance can be improved by allocating the cache memory in a way that it can execute all the transformation within that cache size.

Mainly only those transformations are considered as a bottleneck for performance which uses CACHE.

Say for example:

We are using three transformation in our mapping-
1) Aggregator - using 1 MB for Indexing and 2MB for Data - Lets assume that after using the Aggregator cache calculator the derived value is 6MB.

Likewise use the cache calculator for calculating the cache size of all the transformation(which uses chache)
2) Joiner - 5MB
3) Look-up - 7MB

So, combining the total cache size will be 6MB+5MB+7MB = 18MB

So, minimum of 18MB must be allocated to the session cache. If we are allocating less memory to the session than the Integration service might fail the session itself.

So, for optimizing the session performance CACHE plays an important role.