What indexes will help me in this MERGE query?

Question

I have a table like this:

Name | TimeA | TimeB | ValueA | ValueB 

And I do a few MERGE operations as follows:

  CREATE TABLE #TEMP1... INSERT INTO #TEMP1 SELECT Name, Value FROM dbo.AnotherTable WHERE ... MERGE INTO dbo.MyTable AS Target USING (SELECT Name, Value FROM #TEMP1) AS Source ON Target.Name = Source.Name AND Target.TimeA = @TimeA WHEN MATCHED THEN UPDATE SET ValueA = Value WHEN NOT MATCHED THEN INSERT (Name, TimeA, TimeB, ValueA) VALUES (Source.Name, @TimeA, @TimeB, Value) 

The query execution plan indicates the following:

 MERGE -> Table Merge 3% -> Compute Scalar 0% -> Top 0% -> Compute Scalar 0% -> Compute Scalar 0% -> Nested Loops (Left Outer Join) 0% <- Constant Scan 0% ^ | | --- Compute Scalar 0% <- Table Spool (Kager Spool) 12% <- Table Scan 86% 

The plan, however, does not tell me that the index will improve performance. I think that a non-clustered index on (Name,TimeA) should improve performance. Is there a better way to achieve performance for MERGE queries like this?

EDIT 1 . I have to note the size of the tables. On average, Source always contains 30-70 lines on average, and Target contains> 30 million lines.

Answer 1

I would consider

 WHEN MATCHED AND ValueA <> Value THEN 

Perhaps you are updating records that should not be.

Answer 2

This is here for reference. There are several important points that I used to improve my query:

• Using the optimization suggested by @HLGEM. It made general sense.
• Two relevant items from an MSDN article here

To improve the performance of the MERGE statement, we recommend the following index recommendations:

• Create an index on the join columns in the source table, which is unique and spans.
• Create a unique clustered index on the join columns in the target table.

• Another point from the same MSDN article that suggested not placing constants in the request

To filter rows from source or destination tables, use one of the following methods. Specify a search term to filter strings in the corresponding WHEN clause. For example, WHEN IT IS NOT ACCORDING TO AND S.EmployeeName LIKE 'S%' THEN INSERT ....

Therefore, the indexes I added were:

 ALTER TABLE #TEMP1 ADD CONSTRAINT PK_TEMP1 PRIMARY KEY CLUSTERED (ELEMENTINSTANCE, ifAlias) CREATE CLUSTERED INDEX IX_MyTable ON dbo.MyTable(Name) 

And my last request:

 MERGE INTO dbo.MyTable AS Target USING (SELECT Name, Value FROM #TEMP1) AS Source ON Target.Name = Source.Name WHEN MATCHED AND ValueA <> Value AND Target.TimeA = @TimeA THEN UPDATE SET ValueA = Value WHEN NOT MATCHED THEN INSERT (Name, TimeA, TimeB, ValueA) VALUES (Source.Name, @TimeA, @TimeB, Value) 

This gives me the following execution plan:

 MERGE -> Table Merge 3% -> Compute Scalar 0% -> Top 0% -> Compute Scalar 0% -> Compute Scalar 0% -> Nested Loops (Left Outer Join) 0% <- Table Scan (#TEMP1) Source 12% ^ | | --- Compute Scalar 0% <- Clustered Index Seek (dbo.MyTable) 12% 

Thank you all for your help! Hope this should maintain performance for a while.

Answer 3

I found by spending a lot of time creating indexes that did not improve performance, that when SSMS generates a query plan and does not recommend creating an index, not a single index will help. This is not always the case, but often happens.

Having said that, your problem can be easily solved by trying some indexes, going to Profiler and checking some indicators, such as the total number of views. See what helps and what doesn't. Without looking at the full schema and counting the rows in different tables, we cannot help. In addition, I think that merging often requires table scans, which will not help any index.