topic Re: Converting WHERE clause in Talend Studio

Re: Converting WHERE clause

Anonymous — Wed, 04 Dec 2019 07:55:18 GMT

Hi ,

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').

Attaching screenshot-

Re: Re: Converting WHERE clause

Anonymous — Wed, 04 Dec 2019 09:03:18 GMT

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'"

Regards
Shong

Re: Re: Converting WHERE clause

Anonymous — Wed, 04 Dec 2019 15:02:45 GMT

Hi,

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

Re: Re: Converting WHERE clause

Anonymous — Thu, 05 Dec 2019 06:07:25 GMT

Hi,

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.

Re: Re: Converting WHERE clause

Anonymous — Thu, 05 Dec 2019 06:13:56 GMT

Hi nikhil,

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.

Re: Re: Converting WHERE clause

Anonymous — Thu, 05 Dec 2019 16:01:15 GMT

Hi,

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