First look at the query plan to see what is taking up the time and resources. If you have indexes on mmatter, period that would help. Also, if you have index on cpdc_matternum, you might consider changing the NOT IN clause to a NOT EXISTS clause like this:
AND NOT EXISTS
SELECT * FROM [Hostcrm1].[ReznickGroup_MSCRM].dbo.[CPDC_matter]
WHERE cpdc_matternum COLLATE latin1_general_ci_as = [wa_mattvalue].[mmatter]
But to be honest, these are just educated guesses on my part. You really need to look at the execution plan to see what is slowing it down.
Also, you might want to make sure that the statistics are upto date and that the indexes if any that are being used are not fragmented.