我猜这需要很长时间,因为(a)你正在删除数百万行数据,(b)你把你的日志当做旋转门。仅仅使用EXISTS代替IN或将子查询改为CTE或使用JOIN并不能让执行时间从5-8分钟变为5秒钟。试试吧,我敢打赌也不会更好:
DELETE ml
FROM dbo.MailingListTable AS ml
INNER JOIN dbo.ListItems AS li
ON ml.Md4Hash = li.Md5Hash
INNER JOIN dbo.Lists AS l
ON l.Id = li.ListId
WHERE l.IsGlobal = 1;
问题几乎肯定与执行
DELETE
涉及的I/O有关,而不是用于标识要删除的行的方法。我敢打赌,使用完全相同的数据进行
SELECT
,而不改变索引结构等,并且无论隔离级别如何,都不需要5-8分钟。
那么,怎么解决呢?
首先,请确保日志已调整为处理该大小的事务。
预设日志大小,以便在此类操作期间不必增长,可能是最大大小的两倍。确切的理想大小不是Stack Overflow上的某个人能够告诉您的。
确保自动增长未设置为愚蠢的默认值,例如10%或1MB。自动增长应该是后备方案,但是当您需要它时,它应该仅发生一次,而不是多次以覆盖任何特定活动。因此,请确保它具有固定大小(使大小+持续时间可预测),并且大小合理(因此仅发生一次)。什么是合理的?没有头绪 - 太多的“取决于”。
- 永久禁用缩小日志的任何作业。根据情况处理失控的日志,而不是通过反复缩小日志文件来“防止”日志增长。
接下来,请考虑更改查询以将这些删除批处理为块。您可以根据导致事务持续时间的行数玩弄TOP (?)
参数(即使我们有更多信息,也没有神奇的公式可用于此)。
CREATE TABLE #x
(
Md5Hash SOME_DATA_TYPE_I_DO_NOT_KNOW PRIMARY KEY
);
INSERT #x SELECT DISTINCT li.Md5Hash
FROM dbo.ListItems AS li
INNER JOIN dbo.Lists AS l
ON l.Id = li.ListId
WHERE l.IsGlobal = 1;
DECLARE @p TABLE(p INT SOME_DATA_TYPE_I_DO_NOT_KNOW PRIMARY KEY);
SELECT @rc = 1;
WHILE @rc > 0
BEGIN
DELETE @p;
DELETE TOP (?)
OUTPUT deleted.Md5Hash INTO @p
FROM #x;
SET @rc = @@ROWCOUNT;
BEGIN TRANSACTION;
DELETE ml FROM dbo.MailingListTable AS ml
WHERE EXISTS (SELECT 1 FROM @p WHERE Md5Hash = ml.Md5Hash);
COMMIT TRANSACTION;
END
这可能会延长操作的总时间(特别是如果您在每个循环备份或检查点,或使用
WAITFOR
添加人为延迟,或者两者兼而有之),但应该允许其他事务在块之间偷偷溜进来,等待更短的交易而不是整个过程。此外,由于对日志的影响较小,实际上可能会更快地完成任务。但我必须假设问题不在于花费5-8分钟的时间,而在于它需要5-8分钟
并且阻塞。这应该大大缓解这个问题(如果确实如此,为什么还要关心它需要多长时间?)。
我在这里写了更多关于这种技术的内容。
select distinct dbo.ListItems.Md5Hash
,此外你可以确保MailingListTable的Md5Hash列上有一个键。 - dcaswell