SQL MERGE语句中的UPDATE-no-op [英] UPDATE-no-op in SQL MERGE statement
问题描述
我有一个包含一些持久性数据的表.现在，当我查询它时，我还有一个相当复杂的CTE，它计算结果所需的值，并且需要在持久表中插入缺少的行.最后，我想选择一个结果，该结果由CTE标识的所有行组成，但要包含表中的数据(如果它们已经在表中)，并且我需要有关是否已插入行的信息.
简化后，其工作方式如下(如果您愿意尝试，以下代码将作为普通查询运行):
-- Set-up of test data, this would be the persisted table
DECLARE @target TABLE (id int NOT NULL PRIMARY KEY) ;
INSERT INTO @target (id) SELECT v.id FROM (VALUES (1), (2)) v(id);
-- START OF THE CODE IN QUESTION
-- The result table variable (will be several columns in the end)
DECLARE @result TABLE (id int NOT NULL, new bit NOT NULL) ;
WITH Source AS (
-- Imagine a fairly expensive, recursive CTE here
SELECT * FROM (VALUES (1), (3)) AS Source (id)
)
MERGE INTO @target AS Target
USING Source
ON Target.id = Source.id
-- Perform a no-op on the match to get the output record
WHEN MATCHED THEN
UPDATE SET Target.id=Target.id
WHEN NOT MATCHED BY TARGET THEN
INSERT (id) VALUES (SOURCE.id)
-- select the data to be returned - will be more columns
OUTPUT source.id, CASE WHEN $action='INSERT' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END
INTO @result ;
-- Select the result
SELECT * FROM @result;
我不喜欢WHEN MATCHED THEN UPDATE部分，我宁愿将多余的更新保留下来，但随后在OUTPUT子句中没有得到结果行.
这是完成此类数据并返回数据的最有效方法吗?
还是没有MERGE会有更有效的解决方案，例如通过用SELECT预先计算结果，然后对new=0行执行INSERT?我很难解释查询计划，因为它基本上可以归结为集群索引合并"，与单独的SELECT后跟INSERT变体相比，这对我来说在性能方面非常模糊.而且我想知道SQL Server(带有CU1的2008 R2)是否真的足够聪明，可以看到UPDATE是无操作(例如，无需写操作).
您可以声明一个虚拟变量，并在WHEN MATCHED子句中设置其值.
DECLARE @dummy int;
...
MERGE
...
WHEN MATCHED THEN
UPDATE SET @dummy = 0
...
我相信它应该比实际的表更新便宜.
I have a table with some persistent data in it. Now when I query it, I also have a pretty complex CTE which computes the values required for the result and I need to insert missing rows into the persistent table. In the end I want to select the result consisting of all the rows identified by the CTE but with the data from the table if they were already in the table, and I need the information whether a row has been just inserted or not.
Simplified this works like this (the following code runs as a normal query if you like to try it):
-- Set-up of test data, this would be the persisted table
DECLARE @target TABLE (id int NOT NULL PRIMARY KEY) ;
INSERT INTO @target (id) SELECT v.id FROM (VALUES (1), (2)) v(id);
-- START OF THE CODE IN QUESTION
-- The result table variable (will be several columns in the end)
DECLARE @result TABLE (id int NOT NULL, new bit NOT NULL) ;
WITH Source AS (
-- Imagine a fairly expensive, recursive CTE here
SELECT * FROM (VALUES (1), (3)) AS Source (id)
)
MERGE INTO @target AS Target
USING Source
ON Target.id = Source.id
-- Perform a no-op on the match to get the output record
WHEN MATCHED THEN
UPDATE SET Target.id=Target.id
WHEN NOT MATCHED BY TARGET THEN
INSERT (id) VALUES (SOURCE.id)
-- select the data to be returned - will be more columns
OUTPUT source.id, CASE WHEN $action='INSERT' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END
INTO @result ;
-- Select the result
SELECT * FROM @result;
I don't like the WHEN MATCHED THEN UPDATE part, I'd rather leave the redundant update away but then I don't get the result row in the OUTPUT clause.
Is this the most efficient way to do this kind of completing and returning data?
Or would there be a more efficient solution without MERGE, for instance by pre-computing the result with a SELECT and then perform an INSERT of the rows which are new=0? I have difficulties interpreting the query plan since it basically boils down to a "Clustered Index Merge" which is pretty vague to me performance-wise compared to the separate SELECT followed by INSERT variant. And I wonder if SQL Server (2008 R2 with CU1) is actually smart enough to see that the UPDATE is a no-op (e.g. no write required).
You could declare a dummy variable and set its value in the WHEN MATCHED clause.
DECLARE @dummy int;
...
MERGE
...
WHEN MATCHED THEN
UPDATE SET @dummy = 0
...
I believe it should be less expensive than the actual table update.
这篇关于SQL MERGE语句中的UPDATE-no-op的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
