Optimize lookup table

[Anonymous]

Hi I am new to Talend and have the below scenario to implement.

1) I need to do a look-up on the same (reference) table multiple times (more than 60) with different columns in the source table for each record.

2) My reference table is only of 500 records and creating 60+ look-ups will not make sense seems.

3) i want something where-in i can save this 500 records in temporary memory and call the reference table based upon different columns for each record, which might result in good performance.

Any idea on how to optimize and implement this?

Thanks in advance!

[navds]

I guess you are using a tMap for lookup. This component has an advanced option under "tMap settings" on the lookup table.  Play with "lookup model" and "store temp data" eventually.

You can also store/reuse rows in memory using technical components tHashOutput/tHashInput.

Regards,

[David_Beaty]

Hi,

You probably will need to use the tHashInput/Output components to store the lookup data in memory

[Anonymous]

How will the tHashoutput/tHashinput will be called in tMap for different columns from source?

e.g. if my table has columns C1, C2, C3, C4 ,C5..... C30. Now i want to look-up the reference table for all the columns (C1, C2, C3, C4... C30), what will be the effective way to do it?

[Anonymous]

Hi Harsh,

    In the first read for lookup, you can store all 30 columns of lookup in the Hash Output component.

    After that you can read the same Hash component multiple times for various process using tMap. In each tMap, you can use multiple/different columns for join with main flow as they are independent. The only difference of using Hash component is that its readily available in memory and you don't have to read them again.

    Please refer the screen shot below where same Hash is used as lookup for two joins with different fields used as join conditions.

The only additional step you will have to do is to select the clear hash option during the last step where the Hash is used. This will make sure that Hash is cleared.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂