Thanks for your valuable comments and pointing us to right direction. Will consider all your points. Actually we are going to re-write our application. Could you please elaborate little bit...

Thanks for help. Please find the DDL for 4 tables and sample data. Please let me know if you need any more details.

Thanks for the detailed reply. Herewith attaching the query execution plan. I can see i have missed lot of indexes, is there any other information/data i should look into from...