时间戳

首页 > 解决方案 > 优化 DELETE 查询，其中行包含大数据（以 Base64 序列化的文件）

sql - 优化 DELETE 查询，其中行包含大数据（以 Base64 序列化的文件）

问题描述

当要删除大量行时，有许多关于优化 DELETE 查询的有用技巧。但是，在我的情况下，它并不算多（约 3-4000 行），但每一行都包含在 Base64 中序列化的文件的字段。它们中的大多数都在 10 MB 以上，但也有少数超过 100 MB。DELETE 查询需要 1 个多小时才能完成。有什么技巧可以让它显着加快速度吗？

我尝试过批处理，但由于某种原因实际上使情况变得更糟（更糟的几个小时）。

我暂时禁用了所有约束（ALTER TABLE ? NOCHECK CONSTRAINT all在每个表上），它帮助了大约 10-20 分钟，但它仍然非常缓慢。

我的查询如下所示：

    DECLARE @OrphanedIds TABLE (DocumentId int);
    INSERT INTO @OrphanedIds
        SELECT DocumentId
        FROM Table2
        WHERE ContentType = 'Something';

    DELETE FROM Table2 WHERE DocumentId IN (SELECT DocumentId FROM @OrphanedIds);
    DELETE FROM Table1 WHERE Id IN (SELECT DocumentId FROM @OrphanedIds);

请注意，Table2 包含 Table1 ( ) 的外键Table2.DocumentId = Table1.Id，但如上所述，我暂时禁用了约束。

标签： sqlsql-servertsql

解决方案

首先，使文档 id 成为表中的主键：

DECLARE @OrphanedIds TABLE (DocumentId int PRIMARY TABLE);

然后使用EXISTS(or JOIN) 而不是IN：

DELETE FROM Table2
    WHERE EXISTS (SELECT 1 FROM @OrphanedIds o WHERE table2.DocumentId = o.DocumentId);

DELETE FROM Table1
    WHERE EXISTS (SELECT 1 FROM @OrphanedIds o WHERE table1.DocumentId = o.DocumentId);

您可能会发现级联删除外键可以使数据保持同步——如果您想在将来确保这一点。

推荐阅读