Gentlemen, thank you for your assistance, esp. without knowing my schema.
I forced the join on two tables to be hashed, as the query plan was showing nested loop.
I am currently educating myself further on these internal join processes.
The query runs under a second now.
Thanks!!