topic Re: Join in Sql side or talend side ? in Talend Studio

Join in Sql side or talend side ?

Anonymous — Mon, 10 Sep 2018 08:45:26 GMT

Hi, I have 2 tables, that I need to join in mssql and migrate the data to postgres

Now, there are 2 ways to do it. First, 2 joins the tables using sql in the sql editor section of tmssqlinput and then get the data to migrate to postgres. Second, to have 2 tmssqlinput components , join them in a tmap and then get the data to migrate to postgres

Which among the above I must use? The data in both the tables will be in terms of a million in number. What will be the performance implication if I use the second method, will all the data be loaded in Talend memory and then joined(if yes, if the data is huge, will not Talend crash?).? From what I understand , in sql the join happens in the sql server side and then the joined data comes to Talend

I use this join to do a one time load and also the same join for a delta(in this case , will use an extra condition to get the changed data)

Any help on this please?

Re: Join in Sql side or talend side ?

fdenis — Mon, 10 Sep 2018 09:04:58 GMT

it depend!
you can choose to join on talend (db server is used in production or short in memory)
you can choose to joi on sql to spead up the extraction. (db server is going to work)
I prefer the second option join on sql. Db serveur have to be sized to share his data.
And it will be more efficient for delta.

Re: Join in Sql side or talend side ?

Jesperrekuh — Mon, 10 Sep 2018 11:21:45 GMT

If its in the same database 'always ' on database side... for sure when there're hundred thousand of records in both tables.
An important exception on the above, if you want to join on columns when no indexes are created, then Talend is pretty fast.

I prefer to join in Talend when other database and different sources like file x with tables y needs to be joined.