Any Advice to speed up this query

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.

 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]
 )

avatar image By todd81 16 asked Aug 06, 2015 at 02:00 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By SQLkiwi ♦ 6.6k answered Aug 11, 2015 at 04:05 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x166
x117
x109
x89

asked: Aug 06, 2015 at 02:00 PM

Seen: 72 times

Last Updated: Aug 11, 2015 at 04:05 AM