代码之家 › 专栏 › 技术社区 › Perrier

从CTE中批量删除记录

stored-procedures sql-server sql

0

Perrier · 技术社区 · 7 年前

我需要从最近编写的表中删除旧记录。我的问题是,当另一个进程试图读取或写入表时,delete语句会因死锁而失败。它是以 Transaction isolation level read uncommitted 和 deadlockpriority low 是的。

如何将此CTE和DELETE语句转换为部分删除记录?例如,如果我可以选择cte(x)中为x/500条记录运行delete语句的记录的计数,那么一段时间将是完美的。

  ;with chargesToDelete(id, ciid) as (
        select c.id, ci.Id from @chargeids c
        left join xxx.dbo.chargeitems ci on ci.Charge_Id = c.id
        where ci.id is null
    )
    delete from xxx.dbo.charges
        where Id in (select id from chargesToDelete);

2 回复 | 直到 7 年前

1

EzLo tumao kaixin 7 年前

可以使用session变量循环 @@ROWCOUNT 用一个 TOP N 关于你的 DELETE 是的。

SELECT 1 -- Forces @@ROWCOUNT = 1

WHILE @@ROWCOUNT > 0
BEGIN

    delete TOP (500) I from 
        xxx.dbo.charges AS I
    where 
        exists (
            select 
                'to delete' 
            from 
                @chargeids c
                left join xxx.dbo.chargeitems ci on ci.Charge_Id = c.id
            where 
                ci.id is null AND
                c.id = I.Id)

END

如果你不想用哨兵 SELECT (如果有的话,它会将结果返回给客户机),您可以使用变量。

DECLARE @ForceStart BIT = 1

WHILE @@ROWCOUNT > 0 OR @ForceStart = 1
BEGIN

    SET @ForceStart = 0

    delete TOP (500) I from 
        xxx.dbo.charges AS I
    where 
        exists (
            select 
                'to delete' 
            from 
                @chargeids c
                left join xxx.dbo.chargeitems ci on ci.Charge_Id = c.id
            where 
                ci.id is null AND
                c.id = I.Id)

END

如果子查询需要很长时间才能处理,则可能需要创建一个临时表,其中包含要删除的ID,并在循环中与其联接。

作为一个旁注,如果您正在检查记录的不存在 xxx.dbo.chargeitems ,做一个 NOT EXISTS 会比 LEFT JOIN 具有 IS NULL 是的。

编辑 :使用临时表保存ID:

-- Create temporary table
IF OBJECT_ID('tempdb..#ChargesToDelete') IS NOT NULL
    DROP TABLE #ChargesToDelete

SELECT DISTINCT
    c.id
INTO
    #ChargesToDelete
from 
    @chargeids c
    left join xxx.dbo.chargeitems ci on ci.Charge_Id = c.id
where 
    ci.id is null

CREATE CLUSTERED INDEX CI_ChargesToDelete ON #ChargesToDelete (id)


-- Delete rows in batches
SELECT 1 -- Forces @@ROWCOUNT = 1

WHILE @@ROWCOUNT > 0
BEGIN

    delete TOP (500) I from 
        xxx.dbo.charges AS I
    where 
        exists (select 'to delete' from #ChargesToDelete AS C where c.id = I.Id)

END

2

0

MichaÅ Turczyn 7 年前

您可以尝试以下代码:

declare @deleted table (id int)
declare @amountToDelete int = 2

delete from @deleted
    delete top (@amountToDelete) from xxx.dbo.charges
    output deleted.* into @deleted
    where not exists(select 1 from xxx.dbo.chargeitems
                     where charge_id = xxx.dbo.charges.id)

while (select count(*) from @deleted) = @amountToDelete
begin
    delete from @deleted
    delete top (@amountToDelete) from xxx.dbo.charges
    output deleted.* into @deleted
    where not exists(select 1 from xxx.dbo.chargeitems
                     where charge_id = xxx.dbo.charges.id)
end

@amountToDelete 表示一次要删除的记录批的大小。另一个变量,类型 table ,保留已删除 id 在一个循环中运行。停止循环的条件是删除的记录少于应删除的记录(这意味着有最后一个要删除的记录,并且它们已被删除),这与此条件对应:

while (select count(*) from @deleted) = @amountToDelete

第一次删除在循环之前(外部)执行。如果首先删除所有重新创建的代码,则代码不会进入循环。