Any Advice to speed up this query
The ON clause which I think is causing the problem is below.
MERGE DW.Stage.Survey WITH (HOLDLOCK) AS t USING #source AS s ON ( ( SELECT MAX(LastEditedDttm) FROM ( VALUES (s.Survey_LastEditedDttm) ) AS Dates(LastEditedDttm) ) = t.[MaxLastEditedDttm] AND s.[Survey_Id]= t.[Survey_Id] )
Generally speaking, it's tough to write MERGE queries with even-slightly complicated matching conditions, while guaranteeing correct results. With MERGE, you pretty much have to limit yourself to matching rows using a simple ON clause, with additional criteria in the WHEN clauses. This can be tricky, or even impossible, to achieve reliably in a single query where grouping or row numbering is involved.
99 times out of 100, it's better (faster, more reliable) to write separate insert/update/delete queries. It might sound counter-intuitive, but that is my definite experience with MERGE.