主页 / user-201994

the Ben B's questions

Martin Hope
the Ben B
Asked: 2022-05-18 06:54:46 +0800 CST
  • 5

我有一个需要每天更新的 SQL 表。在更新发生时，可能有也可能没有对该表的查询。它大约有 500,000 行。

当更新表的作业与针对它的查询同时运行时，我们遇到了锁定冲突的问题。

所以我重写了更新表的过程如下：

ALTER procedure [dbo].[Table_Generate] as

declare @d datetime = getdate(), @c as int

--Check temp tables
IF OBJECT_ID('tempdb..#final') IS NOT NULL
    DROP TABLE #final

IF OBJECT_ID('tempdb..#base') IS NOT NULL
    DROP TABLE #base

--Get source data from linked server
select
    ID, 
    Reference, 
    StartDate, 
    EndDate, 
    Description,
    SomeCode
into #base


from    [LinkedServer].[Database].dbo.[A_View]

--Generate row_hash
select 
    ID, 
    Reference, 
    StartDate, 
    EndDate, 
    Description,
    SomeCode,
    hashbytes('SHA2_256',(
     select
        ID, 
        Reference, 
        StartDate, 
        EndDate, 
        Description,
        SomeCode 
     from #base sub where sub.ID = main.ID for xml raw)) as row_hash
    into #final
    from #base main

select @c = count(*) from #final
if @c >0 begin

merge [The_Table_Staging] as target
using #final as source
on source.ID = target.ID

--New rows
when not matched by target then
insert (    RunDate,
            ID, 
            Reference, 
            StartDate, 
            EndDate, 
            Description,
            SomeCode,
            Row_Hash
        ) values (
            @d,
            source.ID, 
            source.Reference, 
            source.StartDate,
            source.EndDate, 
            source.Description,
            source.SomeCode,
            source.row_hash)

--Existing changed rows
when matched and source.row_hash != target.row_hash then update set
     target.RunDate         = @d
    ,target.Reference       = source.Reference
    ,target.StartDate       = source.StartDate
    ,target.EndDate         = source.EndDate
    ,target.Description     = source.Description
    ,target.SomeCode        = source.SomeCode
    ,target.row_hash        = source.row_hash

--Deleted rows
when not matched by source then delete;

--Existing unchanged rows
update [The_Table_Staging] set RunDate = @d where RunDate != @d

--Commit changes
begin transaction

exec sp_rename 'The_Table_Live', 'The_Table_Staging_T'
exec sp_rename 'The_Table_Staging', 'The_Table_Live'
exec sp_rename 'The_Table_Staging_T', 'The_Table_Staging'

commit transaction
end

这个想法是为了减少不必要的行更新，同时也尽量减少对活动表的锁定。我不太喜欢重命名表，但更新/插入需要 5-10 秒，而表重命名几乎是即时的。

所以我的问题是：这种方法可以吗，和/或我可以改进它吗？

谢谢！

编辑以回应 JD：

嗨，JD，请不要道歉 - 我在这里接受建设性的批评。

  1. 我不知道MERGE有问题。我自己从来没有遇到过问题，但是谢谢
  2. 我可能会将这部分重写为单独的INSERT/UPDATE/DELETE语句
  3. 我通常同意。我这样做的原因是，如果我TRUNCATE/INSERT在那个时候从 staging 开始，它需要 6-10 秒，而sp_rename需要不到一秒。所以锁定表的时间更少
  4. 这不会影响表锁定，因为它首先将数据放入临时表。我别无选择，只能使用链接服务器或 SSIS，在这种情况下，我更喜欢链接服务器将所有 SQL 保存在一个地方
  5. 我总是使用XML而不是CONCAT因为否则 'a', 'bc' 的哈希值与 'ab', 'c' 相同，这是不正确的

从登台到填充活动表的所有处理都很好 - 我只想尽量减少最终活动表的锁定时间，

sql-server locking