sql - 将 MERGE 语句替换为插入/删除/更新

Question

我想比较 MERGE 和插入/删除/更新语句之间的性能。我有这个合并：

WITH dest AS
  (SELECT col1,
          coldate,
          col2,
          col3
   FROM dbo.tbl1
   WHERE (col1>col2)AND(col1>col3) )
MERGE dest dst USING
  (SELECT col1,
          coldate,
          col2,
          col3
   FROM dbo.tbl1
   WHERE ISNULL(col3, -1) >= 0 ) src ON (src.col1 = dst.col1)AND(src.coldate=dst.coldate) WHEN NOT MATCHED BY SOURCE THEN
DELETE WHEN NOT MATCHED BY TARGET THEN
INSERT (col1,
        coldate,
        col2,
        col3)
VALUES (src.col1, src.coldate, src.col2, src.col3) WHEN MATCHED AND (ISNULL(src.col3,-1) != ISNULL(dst.col3, -1)) THEN
UPDATE
SET col3 = src.col3

所以我重新插入/删除/更新包含在事务中的语句。它看起来像这样：

BEGIN TRAN UPDATE_TRAN

   ;WITH dest AS ( 
            SELECT col1, coldate, col2, col3
            FROM dbo.tbl1
            WHERE (col1>col2)AND(col1>col3)   
        ) 

UPDATE S
SET col3 = S.col3
FROM (SELECT col1, coldate, col2, col3
                FROM dbo.tbl1
                WHERE ISNULL(col3,-1) >= 0) S INNER JOIN dest 
                ON (S.col1 = dest.col1)AND(S.coldate=dest.coldate)
                AND
                (ISNULL(S.col3,-1) != ISNULL(dest.col3, -1))

COMMIT TRAN UPDATE_TRAN

-- Insert new Rows
BEGIN TRAN INSERT_TRAN

           ;WITH dest AS ( 
            SELECT col1, coldate, col2, col3
            FROM dbo.tbl1
            WHERE (col1>col2)AND(col1>col3)   
        ) 

INSERT INTO dest
SELECT   
            col1, coldate, col2, col3

FROM (SELECT col1, coldate, col2, col3
                FROM dbo.tbl1 
                WHERE ISNULL(col3,-1) >= 0) S

WHERE NOT EXISTS (
        SELECT NULL
        FROM dest 
        WHERE (S.col1 = dest.col1)AND(S.coldate=dest.coldate)
    )
COMMIT TRAN INSERT_TRAN



--Delete Rows
BEGIN TRAN DELETE_TRAN

            ;WITH dest AS ( 
            SELECT col1, coldate, col2, col3
            FROM dbo.tbl1
            WHERE (col1>col2)AND(col1>col3)   
        ) 
DELETE dst
FROM dest dst 
WHERE NOT EXISTS (
        SELECT NULL
        FROM (SELECT col1, coldate, col2, col3
                FROM dbo.tbl
                WHERE ISNULL(col3,-1) >= 0) S
        WHERE (col1>col2)AND(col1>col3) 
    )
COMMIT TRAN DELETE_TRAN

我遇到了问题-性能更好，但结果不同。也许这种方法不正确或其他什么......我不明白，我做错了什么？

标签： sqlsql-server