I want to filter out data(use where clause) from output component for insert update.
I am facing performance issue for inserting/update 5-10 records in talend using tmap.
In my input component, I have only few rows (around 10) and in my output table, I have around 2 million records but I need to scan only 100 records for insert/update logic out of 2 million. My job is scanning whole table which causing performance issue.
details -
table A - has 10 records which I need to insert in table B
table B - has 2 million record but I need to look up only 40 records (where record_type='Full').
Hi You can query 40 records from table B, and do an inner join between table A and table B. table A --main--tMap...update target table |lookup table B in the query of table B "select * from tableB where record_type='Full'"
Could you please also verify whether you have an index for the column record_type? It can be either Binary tree or Bitmap index based on the underlying data. You can do the join condition either using a tMap as shown in above post or you can do the join between two tables in tDBInput component itself using SQL query. This will make sure that only matched records will be coming as output.
Please also check the explain plan to verify the performance of your underlying query.
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
Thanks for the reply. My main table is B itself which I need to update if same record(key) is available else I need to insert a new record. This step is taking time. to complete. Also, in output - (tdbComponent), we don't have option to write query to filter it out.
I can't use this column as indexing. Because this table is main table which collects data from various sources and few sources doesn't have this column/data.
Also, I need to perform insert/update logic and internally generate(calculated fields) values for few column.
Overall time taking is around 3-5 mins. But i think inserting/updating only 10 records at a time should not take more than few seconds. Hence I am looking for some optimization.
Could you please share screenshots of your current Talend job so that we will understand how you are joining the data sets? Please also add the component screenshots of problematic areas.
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
