Any Advice to speed up this query

todd81 2015-08-06 14:00:18

This is a query for loading some survey data from our OLTP to our DataWarehouse . We create a temptable, then populate it with all data from the last 7 days in the survey table via openquery. We then are merging it.

The ON clause which I think is causing the problem is below.

USING #source  AS s 
        SELECT MAX(LastEditedDttm) 
            VALUES (s.Survey_LastEditedDttm)
        ) AS Dates(LastEditedDttm)
    ) = t.[MaxLastEditedDttm]
    AND s.[Survey_Id]= t.[Survey_Id]
SQLkiwi 2015-08-11 04:05:40
It's difficult to be absolutely certain from the anonymized plan, but I'm almost 100% sure that query will not give you the results you are after. The MAX is computed for each value in every row (a somewhat redundant operation, except it rejects any NULLs). Even if the results are somehow correct, given your particular data, the execution plan is pretty horribly inefficient (particularly the Eager Spool).

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.