选择所有数据后,Sql服务器将删除

2022-05-30 00:00:00 sql sql-delete sql-server sql-insert

我添加了一个存储过程,用于删除和插入特定表的数据,但SELECT的执行时间可能需要10分钟才能完成,因此在此期间我的表是空的

如何修改我的存储过程以在它完成时选择所有需要的数据?在目标表上删除当前数据并插入所选数据?

这是我的代码

delete from table_b

insert into table_b(id,name,km)
    select id,t.name,t.kmfrom table_a
    OUTER APPLY (select * from dbo.calculate(table_a.CoordonneeX,table_a.CoordonneeY)) as t

解决方案

打个比方:

  • 你在当地农贸市场卖的蔬菜摆满了桌子。
  • 当你带来一批新的蔬菜时,你需要20分钟来清理桌子,用新的产品取代库存。
  • 您不希望客户坐在那里等20分钟等待切换发生(大多数人只会从其他人那里购买蔬菜)。

如果您有第二张空桌子,在那里您装满了新蔬菜,而在这样做的同时,客户仍然可以从第一张桌子上买到较旧的蔬菜,情况会怎样?(让我们假设这不是因为旧蔬菜变坏了,或者其他原因不太受欢迎。)

有多种方法可以满足您的场景。基本概念是:

  • 您有第二个在后台加载的影子表。
  • 当此后台加载发生时,用户将继续看到第一个表中较旧的数据。
  • 后台加载完成后,您可以通过以下方式将用户重定向到数据更新的第二个表:
    • 重命名
    • 更改同义词或视图以指向新表
    • 在架构之间传输(请参阅here和here)
    • partition switching(尽管普遍认为,不需要企业版)
我一直喜欢使用模式来解决这一问题,但分区切换是最好的解决方案,因为其他三种解决方案需要更积极的模式修改锁,不允许以较低的优先级等待,并且有更大的风险使任何现有的统计/执行计划无效或不那么准确。所有这些解决方案的一个问题是,如果有指向主表的外键,则无论您的计划的一部分是清空父表,都必须处理这一问题。

大量借用Kendra的gist,因为我很懒,而她在那里做得很好,让我们创建原始表的两个副本,一个用于处理传入的新数据,另一个用于接受旧数据:

CREATE TABLE dbo.MyTable
(
  id int NOT NULL,
  description varchar(32)
);

INSERT dbo.MyTable(id, description) VALUES(1, 'old data');

CREATE TABLE dbo.MyTable_Staging
(
  id int NOT NULL,
  description varchar(32)
);

CREATE TABLE dbo.MyTable_Garbage
(
  id int NOT NULL,
  description varchar(32)
);

SELECT * FROM dbo.MyTable;

(这是一个非常简单的模型--当然,您的实际表会有匹配的主键、索引、约束等。)

现在,我们可以在后台加载临时表,完成后,将当前数据切换到垃圾表,然后将临时表切换到主表。

-- perform the background loading outside of any blocking transaction:

TRUNCATE TABLE dbo.MyTable_Garbage;
TRUNCATE TABLE dbo.MyTable_Staging;
INSERT dbo.MyTable_Staging(id, description) 
  VALUES(1, 'new data'),(2, 'new row!');

BEGIN TRANSACTION;

  ALTER TABLE dbo.MyTable
    SWITCH TO dbo.MyTable_Garbage
    WITH ( WAIT_AT_LOW_PRIORITY 
      ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
  );  

  ALTER TABLE dbo.MyTable_Staging
    SWITCH TO dbo.MyTable;  
    
COMMIT TRANSACTION;
    
SELECT * FROM dbo.MyTable;
我在db<>fiddle中演示了这一点,只是那里的权限不允许我们为拦截器指定等待较低优先级选项,这在规模上将是重要的。为了简单起见,也没有错误处理,但这并不意味着不需要它。(感谢@CharlieFaces指出,PARTITION 1对于未分区的表是不必要的。)

您也可以更快地清理垃圾,就像在提交后立即清理垃圾一样,但保留垃圾可以让您在出现某种问题时进行故障排除或恢复。

相关文章