How do you want us to help? - need help to optimize the query.

Can you drop this proc and never use? - not possible

If not, what are the real requirements? - requirement is to get all record that satisfy the join criteria

It's impossible to advise on something like that - too little information. - can u tell me what information do you need