搜索是由同一源表上的有序扫描驱动的,因此看起来应该最终以相同的顺序寻找相同的值.

两个嵌套循环都具有< NestedLoops Optimized =“false”WithOrderedPrefetch =“true”>

任何人都知道为什么这个任务的成本在第一个计划中为0.172434而在第二个计划中为3.01702？

(问题的原因是第一个查询被建议给我作为优化,因为计划成本明显低得多.它实际上看起来好像它做了更多的工作,但我只是试图解释这种差异.. .)

建立

CREATE TABLE dbo.Target(KeyCol int PRIMARY KEY,OtherCol char(32) NOT NULL);

CREATE TABLE dbo.Staging(KeyCol int PRIMARY KEY,OtherCol char(32) NOT NULL); 

INSERT INTO dbo.Target
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY @@SPID),LEFT(NEWID(),32)
FROM master..spt_values v1,master..spt_values v2;

INSERT INTO dbo.Staging
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY @@SPID),32)
FROM master..spt_values v1;

查询1 “Paste the plan” link

WITH T
     AS (SELECT *
         FROM   Target AS T
         WHERE  T.KeyCol IN (SELECT S.KeyCol
                             FROM   Staging AS S))
MERGE T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol,OtherCol )
  VALUES(S.KeyCol,S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

查询2 “Paste the plan” link

MERGE Target T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol,OtherCol )
  VALUES( S.KeyCol,S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

查询1

查询2

以上是在sql Server 2014(SP2)(KB3171021) – 12.0.5000.0(X64)上测试的

@Joe Obbish在评论中指出一个更简单的复制品

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN Target AS T 
    ON T.KeyCol = S.KeyCol;

VS

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN (SELECT * FROM Target) AS T 
    ON T.KeyCol = S.KeyCol;

对于1,000行临时表,上述两者仍然具有相同的计划形状和嵌套循环,而没有派生表的计划看起来更便宜,但对于10,000行临时表和上述相同的目标表,成本差异确实改变了计划形状(具有完全扫描和合并连接似乎比昂贵的成本搜索相对更具吸引力)显示这种成本差异可能具有其他影响,而不仅仅是使比较计划更难.