WITH (nolock) isn't a magic "go faster" button. It's allowing dirty reads. If you want it to read faster, index the columns in the joins and the filters.
We don't even know what your query is supposed to do, and we don't have any scripts for the tables involved. Is this a game of pin the tail on the donkey?
Maybe read this
- This reply was modified 4 years, 7 months ago by pietlinden. Reason: additional info