sql-server – 没有数据更改的UPDATE性能

例如,UPDATE 1和UPDATE 2之间的执行速度有以下不同之处：

CREATE TABLE MyTable (ID int PRIMARY KEY,Value int);
INSERT INTO MyTable (ID,Value)
VALUES
    (1,1),(2,2),(3,3);

-- UPDATE 1
UPDATE MyTable
SET
    Value = 2
WHERE
    ID = 2
    AND Value <> 2;
SELECT @@ROWCOUNT;

-- UPDATE 2
UPDATE MyTable
SET
    Value = 2
WHERE
    ID = 2;
SELECT @@ROWCOUNT;

DROP TABLE MyTable;

我问的原因是我需要行数包括未更改的行,所以我知道如果ID不存在则是否进行插入.因此我使用了UPDATE 2表格.如果使用UPDATE 1表单有性能优势,是否可以以某种方式获取我需要的行数？

解决方法

If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state),is there any performance benefit in putting a check in the where clause to prevent the update?

由于UPDATE 1,可能存在轻微的性能差异：

>实际上没有更新任何行(因此没有写入磁盘,甚至没有最小的日志活动),以及
>取出比实际更新所需的限制更少的锁(因此更好地实现并发)(请参阅最后的更新部分)

但是,您的系统需要使用模式,数据和系统负载来衡量有多大差异.有几个因素会影响非更新UPDATE的影响程度：

>正在更新的表上的争用量
>正在更新的行数
>如果正在更新的表上有UPDATE触发器(如Mark在对问题的评论中所述).如果执行UPDATE TableName SET Field1 = Field1,则会触发更新触发器并指示该字段已更新(如果使用UPDATE()或COLUMNS_UPDATED函数进行检查),并且INSERTED和DELETED表中的字段都是相同的值.

此外,以下摘要部分可在Paul White的文章The Impact of Non-Updating Updates中找到(正如@spaghettidba在评论中所述)：

SQL Server contains a number of optimisations to avoid unnecessary logging or page flushing when processing an UPDATE operation that will not result in any change to the persistent database.

• Non-updating updates to a clustered table generally avoid extra logging and page flushing,unless a column that forms (part of) the cluster key is affected by the update operation.
• If any part of the cluster key is ‘updated’ to the same value,the operation is logged as if data had changed,and the affected pages are marked as dirty in the buffer pool. This is a consequence of the conversion of the UPDATE to a delete-then-insert operation.
• Heap tables behave the same as clustered tables,except they do not have a cluster key to cause any extra logging or page flushing. This remains the case even where a non-clustered primary key exists on the heap. Non-updating updates to a heap therefore generally avoid the extra logging and flushing (but see below).
• Both heaps and clustered tables will suffer the extra logging and flushing for any row where a LOB column containing more than 8000 bytes of data is updated to the same value using any syntax other than ‘SET column_name = column_name’.
• Simply enabling either type of row versioning isolation level on a database always causes the extra logging and flushing. This occurs regardless of the isolation level in effect for the update transaction.

请记住(特别是如果你不按照链接查看Paul的完整文章),请注意以下两项：

>非更新更新仍有一些日志活动,表明事务正在开始和结束.只是没有数据修改发生(这仍然是一个很好的节省).
>如上所述,您需要在系统上进行测试.使用Paul正在使用的相同研究查询,看看是否得到相同的结果.我看到我的系统结果与文章中显示的结果略有不同.仍然没有要写的脏页,但是更多的日志活动.

… I need the row count to include the unchanged row so I know whether to do an insert if the ID does not exist. … is it possible to get the row count that I need somehow?

简单地说,如果您只处理单行,则可以执行以下操作：

UPDATE MyTable
SET    Value = 2
WHERE  ID = 2
AND Value <> 2;

IF (@@ROWCOUNT = 0)
BEGIN
  IF (NOT EXISTS(
                 SELECT *
                 FROM   MyTable
                 WHERE  ID = 2 -- or Value = 2 depending on the scenario
                )
     )
  BEGIN
     INSERT INTO MyTable (ID,Value) -- or leave out ID if it is an IDENTITY
     VALUES (2,2);
  END;
END;

对于多行,您可以使用OUTPUT子句获取做出该决定所需的信息.通过准确捕获更??新的行,您可以缩小项目以查找不知道不更新不存在的行与不更新存在但不需要更新的行之间的区别.

我在以下答案中展示了基本实现：

How to avoid using Merge query when upserting multiple data using xml parameter?

该答案中显示的方法不会过滤掉现有但不需要更新的行.可以添加该部分,但首先需要准确显示要合并到MyTable的数据集的位置.他们来自临时餐桌吗？表值参数(TVP)？

更新1：

我终于能够进行一些测试,这是我发现的有关事务日志和锁定的内容.首先,表的架构：

CREATE TABLE [dbo].[Test]
(
  [ID] [int] NOT NULL CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED,[StringField] [varchar](500) NULL
);

接下来,测试将字段更新为已有的值：

UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117

结果：

-- Transaction Log (2 entries):
Operation
----------------------------
LOP_BEGIN_XACT
LOP_COMMIT_XACT


-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
8 - IX          6 - PAGE
5 - X           7 - KEY

最后,由于值不变而过滤掉更新的测试：

UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117
AND    rt.StringField <> '04CF508B-B78E-4264-B9EE-E87DC4AD237A';

结果：

-- Transaction Log (0 entries):
Operation
----------------------------


-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
7 - IU          6 - PAGE
4 - U           7 - KEY

如您所见,过滤掉行时没有任何内容写入事务日志,而是标记事务开始和结束的两个条目.虽然这两个条目几乎都没有,但它们仍然存在.

此外,在过滤掉未更改的行时,PAGE和KEY资源的锁定限制较少.如果没有其他进程与此表交互,那么它可能不是问题(但这有多大可能,真的吗？).请记住,任何链接博客(甚至我的测试)中显示的测试都隐含地假设表上没有争用,因为它从不是测试的一部分.说不更新的更新是如此轻量级,以至于无需支付费用,因为在真空中或多或少地进行了测试,因此需要花费大量的时间进行过滤.但在制作中,这张表很可能不是孤立的.当然,很可能一点点的日志记录和限制性更强的锁定都不会降低效率.那么回答这个问题最可靠的信息来源呢？ SQL Server.特别是：您的SQL Server.它会告诉你哪种方法对你的系统更好:-).

更新2：

如果新值与当前值相同的操作(即无更新)输出新值不同且需要更新的操作,那么以下模式可能会更好,特别是如果桌上有很多争论.我们的想法是先做一个简单的SELECT来获取当前值.如果你没有得到一个值,那么你有关于INSERT的答案.如果您有值,则可以执行简单的IF并仅在需要时发出UPDATE.

DECLARE @CurrentValue VARCHAR(500) = NULL,@NewValue VARCHAR(500) = '04CF508B-B78E-4264-B9EE-E87DC4AD237A',@ID INT = 4082117;

SELECT @CurrentValue = rt.StringField
FROM   dbo.Test rt
WHERE  rt.ID = @ID;

IF (@CurrentValue IS NULL) -- if NULL is valid,use @@ROWCOUNT = 0
BEGIN
  -- row does not exist
  INSERT INTO dbo.Test (ID,StringField)
  VALUES (@ID,@NewValue);
END;
ELSE
BEGIN
  -- row exists,so check value to see if it is different
  IF (@CurrentValue <> @NewValue)
  BEGIN
    -- value is different,so do the update
    UPDATE rt
    SET    rt.StringField = @NewValue
    FROM   dbo.Test rt
    WHERE  rt.ID = @ID;
  END;
END;

结果：

-- Transaction Log (0 entries):
Operation
----------------------------


-- SQL Profiler (2 Lock:Acquired events):
Mode            Type
--------------------------------------
6 - IS          5 - OBJECT
6 - IS          6 - PAGE

因此,只获取了2个锁而不是3个,这两个锁都是Intent Shared,而不是Intent eXclusive或Intent Update(Lock Compatibility).请记住,获取的每个锁也将被释放,每个锁实际上是2个操作,因此这个新方法总共有4个操作,而不是最初提出的方法中的6个操作.考虑到该操作每15ms运行一次(大约如O.P.所述),即每秒约66次.因此,原始提案相当于每秒396次锁定/解锁操作,而这种新方法相当于每秒仅进行264次锁定/解锁操作甚至更轻量级的锁定.这不是一个令人敬畏的性能的保证,但肯定值得测试:-).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!