DSXchange

One lookup stage can only designate 1 reference link for multiple rows so you can only do range lookups on whatever reference link is designated. You can do two range lookups against 1 reference link. Since you want to do range lookups on 2 different reference links, you will have to use 2 lookup stages.

Mike

Mike wrote:Since you want to do range lookups on 2 different reference links, you will have to use 2 lookup stages.

Thanks Mike for the clear response, it really helped. In my case I use range lookups heavily (up to seven range lookups per job) along with hash file sets as reference stage for better performance.

Would it impact the performance if I used a separate lookup for each reference link (in this case seven lookup stages)? If yes, what factors should I take into consideration to avoid such an impact.

Thanks.

I doubt that you'd see any noticeable performance difference between a pipeline of 7 lookup stages and a single lookup stage with 7 reference links.

The important thing is you will get 7 of 7 correct results instead of 1 of 7.

It would be an interesting exercise to compare the job score between the 2 designs to see if operator combination combines any of the lookups.

Mike

Mike wrote:It would be an interesting exercise to compare the job score between the 2 designs to see if operator combination combines any of the lookups.

Agree. I will make the exercise and post the results. I will mark post as resolved for now.

Thanks for your support