Editorial / Best Answer
Answered by:
prodyot Sarkar
The following reasons for using lookups.....
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.
Why we use lookup transformations?
Get a related value-Get the Employee Name from Employee table based on the Employee IDPerform Calculation.
Update slowly changing dimension tables - We can use unconnected lookup transformation to determine whether the records already exist in the target or not.
Editorial / Best Answer
Answered by: prodyot Sarkar
The following reasons for using lookups.....
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.
Related Answered Questions
Related Open Questions