SQL Server:更快地使用NULL FK删除行

Question

我有一个巨大的表TblTraffic，它有许多列：

Id, date, year, month, day, ReferenceId, data1, data2, data3....

该表的行超过10亿，大小约为1TB。它也有许多索引和fk。

随着时间的流逝，我们要清除一些旧数据。但是，事实证明，在这种情况下，删除很慢。经过一番研究，我们发现这是因为一个FK（ReferecenId）。因此，我们放下FK。现在，该表的批处理删除速度要快得多。

fk来自表tblReference，大约有2亿行：

ReferenceId, TrafficId, data1, data2, ...

现在，因为我们将FK丢弃到 tblTraffic中的批处理删除数据，因此tblReference中的某些行在tblTraffic中具有null FK。我们想从tblReference删除所有这些。tblReference中没有这样的行。

DELETE *
FROM tblReference 
WHERE NOT EXISTS (SELECT 1
                  FROM tblTraffic
                  WHERE tblTraffic.Id = tblReference.TrafficId)

我们尝试为此尝试批处理删除，但是很慢。对此有任何建议吗？

我们正在使用SQL Server和C＃。

谢谢

Answer 1

您可以在上述列上放置索引，然后相应删除（如果您有足够的资源）。

，如果您禁用删除操作的不需要索引，也更快地删除工作。

，如果您将删除操作分为块，它将更加友好。

您也可以考虑使用degree of parallelism

Answer 2

作为替代选项，您可以尝试使用LEFT JOIN ED与WHERE ... IS NULL组合。

DELETE ref
FROM 
    tblReference ref
    LEFT JOIN tblTraffic tra
        ON tra.Id = ref.TrafficId
WHERE tra.TrafficId IS NULL

根据您的实际数据库设置，这可能比嵌套子查询更快。

我还建议在运行此查询之前，在表tblTraffic中的Id列上设置索引。

另一种优化将是在DELETE期间暂时禁用表tblReference上的所有约束和索引（包括tblReference.TrafficId上的潜在索引）：这将防止RDBMS在删除过程中重新计算索引，如果有很多，那么如果很多，则该索引是昂贵的删除行。