Number of Join in a Job

verify · Post by **verify** » Thu Feb 12, 2009 11:28 am

1) I have requirement where i have to join with some tables and views.
For a Job atmost we're using 5 join stages and 10 sort stages( since the pre-requirement for join stage is to sort the data).

Since i have 5 join stages with two inputs, for each input one sort stage so total will be 10 sort stages.And we're joining on different keys and same keys again also, based on the business requirement.

I want to know whether such kind of design will be reliable or not.
If not, then please tell me how many number of join and lookup stage i've to use per job.

2) In some cases i'm joing multiple sources(more than two) with one Join stage, will this design affect the job peformance or not?

3) I heard that we shouldn't use multiple Join stages on same keys in one job, since this affects the job performance..
Am i right ?

Please help me out..
Any help will be greatly appreciated..

Regards

ray.wurlod · Post by **ray.wurlod** » Thu Feb 12, 2009 2:09 pm

1) Yes, for some value of "reliable" and if you have sufficient resources on the server.
2) Yes - any load will "affect performance" (whatever "performance" means)
3) Who told you that?

verify · Post by **verify** » Thu Feb 12, 2009 9:16 pm

Thanks Ray for your response..

3) I came to know from my colleague... just want to confirm with the DS Experts before accepting that..

Could you please let me know the best feasible design for Scenario 3..

Thanks..

ray.wurlod · Post by **ray.wurlod** » Thu Feb 12, 2009 10:26 pm

The best scenario for scenario 3 (need more than one Join stage) is to use more than one Join stage. Provided your system has the resources, there is no limit on the number you can have. But that's a major proviso.