Optimize lookup table

Four Stars

Optimize lookup table

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!

Highlighted
Seven Stars

Re: Optimize lookup table

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.

Selection_006.png

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

 

Regards,

Nine Stars

Re: Optimize lookup table

Hi,

 

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

 

Regards David
Dont forget to give Kudos when an answer is helpful or the solution.
Four Stars

Re: Optimize lookup table

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?

 

 

Employee

Re: Optimize lookup table

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.

image.png

 

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 :-)

 


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.