从表中删除Orphan记录

Question

我有一个表(比如Table1)，它的主键是与其他10个表有外键关系。所有这些表都有数百万的数据。我需要以一种性能有效的方式从Table1中删除所有的孤立记录。此外，脚本不应该阻止对这些表的写入。有人能帮我查询一下吗？

我正在使用Server 2014

Answer 1

这就是我现在正在尝试的：

SET NOCOUNT ON

DECLARE @totalcnt INT
        ,@Batch INT = 1000

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

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

CREATE TABLE #TEMP (TableKey int NOT NULL PRIMARY KEY)
CREATE TABLE #TEMPWrk (TableKey int NOT NULL PRIMARY KEY)

INSERT INTO #TEMP
SELECT
    A.TableKey
FROM
    dbo.Table1 A
        LEFT JOIN (
                    SELECT TableKey FROM dbo.Table2 UNION
                    SELECT TableKey FROM dbo.Table3 UNION
                    SELECT TableKey FROM dbo.Table4 UNION
                    SELECT TableKey FROM dbo.Table5 UNION
                    SELECT TableKey FROM dbo.Table6 UNION
                    SELECT TableKey FROM dbo.Table7 UNION
                    SELECT TableKey FROM dbo.Table8
                    ) T ON T.TableKey = A.TableKey
WHERE
    T.TableKey IS NULL


SELECT 
    @totalcnt = COUNT(*)
FROM
    #TEMP

WHILE (@totalcnt > 0)
BEGIN
    PRINT @totalcnt
    DELETE TOP (@batch)
    FROM
        #TEMP
    OUTPUT DELETED.TableKey INTO #TEMPWrk   

    DELETE T
    FROM
        dbo.Table1 T
            JOIN #TEMPWrk A ON T.TableKey = A.TableKey

    DELETE FROM #TEMPWrk

    SELECT 
        @totalcnt = COUNT(*)
    FROM
        #TEMP
END

SET NOCOUNT OFF

Answer 2

我做了类似的删除如下：

1. 根据硬件场景，确定删除表上的聚集索引键，并按10k至100 k的批次删除。
2. 如果在这些表上有触发器、CT、CDC、索引视图，那么我们需要在删除期间删除或禁用，这将使删除速度更快。
3. 在删除操作中，我将“恢复模式”简化为简单。
4. 在一些批删除过程中调用显式“检查点”。

对于任何给定的场景，如果不允许在PROD中执行上述任何步骤，则联机删除将分别较慢。

Answer 3

由于这两个表中都有事务，并且为了最小化锁定，您可以应用Divide并征服的原则

1-获取表1的最大id (父表)

2-分阶段运行以下脚本，例如每次迭代100000行

DELETE FROM tabl2  t2
WHERE  t2.id  < 100000
AND    t2.id  IN (SELECT t1.id FROM table1 t1)

3-对其他范围重复step2

    where  t2.id BETWEEN  100001 AND 200000 

最高达最大值id

可以使用while语句自动执行这些步骤。