In the queries below both execution plans are estimated to perform 1,000 seeks on a unique index.
The seeks are driven by an ordered scan on the same source table so seemingly should end up seeking the same values in the same order.
Both nested loops have <NestedLoops Optimized="false" WithOrderedPrefetch="true">
Anyone know why this task is costed at 0.172434 in the first plan but 3.01702 in the second?
(The reason for the question is that the first query was suggested to me as an optimisation due to the apparent much lower plan cost. It actually looks to me as though it does more work but I'm just attempting to explain the discrepancy...)
Setup
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), LEFT(NEWID(),32)
FROM master..spt_values v1;
Query 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;
Query 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;
Query 1
Query 2
The above was tested on SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
@Joe Obbish points out in the comments that a simpler repro would be
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;
For the 1,000 row staging table both of the above still have the same plan shape with nested loops and the plan without the derived table appearing cheaper, but for a 10,000 row staging table and same target table as above the difference in costs does change the plan shape (with a full scan and merge join seeming relatively more attractive than expensively costed seeks) showing this cost discrepancy can have implications other than just making it harder to compare plans.