Performance impact of Keep and Join

[SharathPanackal]

Hi All,

I had a discussion with one of my fellow developers  regarding the merits and demerits of using Join and Keep. I understand the output of Join and Keep. I fail to understand the performance impact of using Join or Keep in large datasets  and which to prefer in such cases. 

Please share your understanding on the performance impacts. Thanks.

[Somasundaram]

Hi,

Keep and join is for different purpose.

Keep is for  one to many or many to many scenario and Joins for one to one scenario.

[marcus_sommer · Accepted solution]

Even if the transformation-logic from join and keep are similar it are complete different approaches because a join merged tables horizontally and a keep filters one table based on the records of another table. Therefore there is no choice between them else you need to apply those one which leads you to your target datamodel.

In regard to performance especially in large datsets there are alternatives possible which might be more suitable. By the join it would be mapping (even multiple and/or nested mappings are often faster as a join) and by keep it might be a where-clause with exists(). But like always it will depend on the data and the requirements which methods are more suitable.

- Marcus