topic [resolved] Filter in the source in Talend Studio

[resolved] Filter in the source

Anonymous — Sat, 16 Nov 2024 10:03:25 GMT

Hi team,
Now, I got two sources by tMSSqlInput and joined them in the tMap. I encountered a performance issue when i ran the subjob. The issue is which need to get all data than go to join. I think it's very slow.
I want to do the filter in the tMSSqlInput, but i am not sure whether i can do it or not. Or do you have any idea for the issue?

This is script that i can NOT do the join in it.

Only do the join in the tMap.

Please help me to review it, thanks.

Re: [resolved] Filter in the source

Anonymous — Mon, 06 Mar 2017 08:32:00 GMT

Hi,
The tMap component needs load all data into local firstly and then doing match.
Depending on the filters you are using you could use ELT components to let the DBMS handle the filtering. Have you already checked talend ELT components for sql server? tELTMSsqlInput, tELTMSSqlOutPut and tELTMSSqlMap.
Best regards
Sabrina

Re: [resolved] Filter in the source

Anonymous — Fri, 17 Mar 2017 10:02:21 GMT

I think i can NOT use tELTMSSql component because my source data is from different Database.

Re: [resolved] Filter in the source

vapukov — Fri, 17 Mar 2017 13:32:41 GMT

Filter in tMySQLInput - it is normal WHERE in SQL code - so this is You can do easily,
but main idea of ELT - load data to destination and than compare and transform
ok, You are have 50 M in Lookup table, but how many in new(LoadPackage...)?

Re: [resolved] Filter in the source

Anonymous — Mon, 20 Mar 2017 02:35:52 GMT

Hi vapukov,
There are about 1000 rows in LoadPackage... which just is one day data.
But in the LoadRateFulfill.... WHERE conditions are from LoadPackage... result, so i have no idea how to do it.

Re: [resolved] Filter in the source

Anonymous — Fri, 24 Mar 2017 10:34:39 GMT

I have fixed the issue, just share my solution.
In the tMap , set Lookup Model to "Reload at each row" and add golobalMap variables.

Filter in tMSSqlInput, use WHERE in SQL code with golbalMap.