I am using a lookup transformation to compare my source and target data both are from different Databases and we are Using the Source_System_id Column to do the comparison. Since This value for Source_System_id is derived using TO_CHAR(PERSON_KEY) || '~' || SOURCE_CD || '~' || TYPE_CD in target , In lookup i am writing an lookup override query to replicate the same . The Source_System_id column in target is indexed and it is the primary key, But when I try to use a lookup to compare it with the source table the performance is very slow and it takes time in Building the cache can anyone please suggest how to improve the performance.
1 Answers
It seems like you have lot of data to cache. So, here is what i am suggesting.
Remove all unwanted ports from the lookup. Remove unwanted ORDER BY clause too from the LKP SQL overwrite and keep only the key.
First of all collect LKP SQL from session log and run it in DB and see if its returning all the data in lookup within expected time. If this SQL takes time then the SQL has the problem. And try to tune it up.
2.1 - You can add some filter on date to limit cached data.
2.2 - You can also apply some other type of filter to limit cached data.
2.3 - Join with target or some other table to limit data.
2.4 - If above options are impossible, create a new mapping to stage the data into a table in target with only source_id and run it before main mapping. And then join that small stage table with lookup main table to limit the data. 1.5 - if all above fails or not possible to achieve, you can add some index or hint to make the SQL faster.Do not concatenate in lookup query( i think you arent doing it, but still saying).
if you cant achieve above points or they dont help, then you can increase the memory of the lookup buffer size.
Increase number of concurrent lookup sometime helps.
Related