Performance tuning in DS for Join..

devesh_ssingh · Post by **devesh_ssingh** » Sat May 22, 2010 5:15 am

Hello Mentors,

Hope you doing well.
i have somthing to discuss relating join stage and its performance.

Its lot been dicussed but i would like to know how would optimize more in JOIN stage of DS...

lets take a scenerio
Join two tables each having 10 millions records....inner join
what i did is sorted them by qyerying to db and then again applied hash partition on 2-node system....i have flexibilty to select 4-node, 8-node
as well...

let me know if i have acheived great level of optimization or i can improve it further.

Thanks,
Devesh

ray.wurlod · Post by **ray.wurlod** » Sat May 22, 2010 9:30 am

DataStage is unnecessarily re-sorting your data. Add a Sort stage to each input link to the Join stage. In each Sort stage specify "don't sort (already sorted)" for the sort key columns.

arun_im4u · Post by **arun_im4u** » Tue May 25, 2010 9:25 am

Ray,

Wouldn't the Stable sort option in the Join stage do the same function?

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Tue May 25, 2010 12:35 pm

The Stable option doesn't mean "no sort" - it means "if you have multiple records with the same identical keys, keep them in the same order going out that they were coming in", something that isn't guaranteed otherwise.

arun_im4u · Post by **arun_im4u** » Tue May 25, 2010 2:44 pm

Thanks for the clarification.