改善DELETE查询的不良性能 [英] Improve bad performance of a DELETE query

问题描述

IF OBJECT_ID('tempdb..#BigTable') IS NOT NULL BEGIN DROP TABLE #BigTable END
CREATE TABLE #BigTable (Col1 INT, Col2 INT, CONSTRAINT [PK_BigTable] PRIMARY KEY CLUSTERED ([Col2] ASC))
CREATE INDEX IX_BigTable_Col1 ON #BigTable (Col1)

IF OBJECT_ID('tempdb..#RefTable') IS NOT NULL BEGIN DROP TABLE #RefTable END
CREATE TABLE #RefTable (Col1 INT, Col2 INT, CONSTRAINT [PK_RefTable] PRIMARY KEY CLUSTERED ([Col1] ASC))

我有一个使用表格的查询与上述类似。我必须做一个现在看起来像这样的查询：
$

I have a query that uses table that are like the above. I have to do a query that now looks like this:

DELETE b FROM #BigTable b
WHERE b.Col1 = @Col1 AND NOT EXISTS(SELECT 1 FROM #RefTable r WHERE r.[Col2] = b.[Col2] AND r.[Col1] = @Col1);

我道歉我不知道如何填充这些表进行测试，但是如果有人能给我一些想法如何以不同的方式写出来 - 大表几乎是100,000,000行，RefTable大约是20,000,000。我应该删除BigTable中不在RefTable中的所有行
。现有的查询性能很糟糕。我理解使用临时表存储我想要删除的行可能是明智的，但我不明白该怎么做。实际的表在
中有更多列，而不是临时表。任何帮助将不胜感激如何重写查询。

I apologize I am not sure how to populate these tables for the test, but if someone can give me some ideas how to write this differently - the big table is almost 100,000,000 rows and the RefTable is about 20,000,000. I am supposed to delete all the rows in BigTable that are not in the RefTable. The existing query performance terrible. I understand that it may be wise to use a temp table to store the rows I do want to delete, but I don't understand quite how to do it. The actual tables have more columns in them and are not temp tables. Any help would be appreciated as to how to rewrite the query.

推荐答案

这取决于您是希望删除速度更快，还是希望删除更少。较小的块阻塞较少，但通常需要更多的总时间，您需要查看系统上的工作原理。

It depends if you want the delete to go faster, or you want it to block less. Smaller chunks block less but usually take more total time, you'll need to see what works on your system.

您可以将需要删除的所有ID插入到临时表中。

You could insert all the IDs you need deleted into a temp table.

SELECT TOP 100000 Col1 INTO #TMP FROM #BigTable b with（nolock）

WHERE b.Col1 = @ Col1 AND NOT EXISTS（SELECT 1 FROM #RefTable r 在哪里r。[Col2] = b。[Col2] AND r。[Col1] = @ Col1）;

SELECT TOP 100000 Col1 INTO #TMP FROM #BigTable b with (nolock)
WHERE b.Col1 = @Col1 AND NOT EXISTS(SELECT 1 FROM #RefTable r  WHERE r.[Col2] = b.[Col2] AND r.[Col1] = @Col1);

如果需要，在临时表上创建一个索引，

Create an index on the temp table if needed,

然后从加入中删除

如果你需要自动化你可以把它放在一个while循环中，例如：

If you need to automate this you can put it in a while loop with something like:

SELECT TOP 10000000 Col1 INTO #TMP from #BigTable b with（nolock ）b $ C $ b WHERE b.Col1 = @ Col1 AND NOT EXISTS（SELECT 1 from #RefTable r  WHERE r。[Col2] = b。[Col2] AND r。[Col1] = @ Col1）;

SELECT TOP 10000000 Col1 INTO #TMP FROM #BigTable b with (nolock)
WHERE b.Col1 = @Col1 AND NOT EXISTS(SELECT 1 FROM #RefTable r  WHERE r.[Col2] = b.[Col2] AND r.[Col1] = @Col1);

WHILE（@currentrows<> 0）

WHILE (@currentrows <>0)

BEGIN

DELETE TOP 10000 l

FROM #BigTable l

INNER JOIN #tmpId tmp

ON l.col1 = tmp.col1

DELETE TOP 10000 l
FROM #BigTable l
INNER JOIN #tmpId tmp
ON l.col1= tmp.col1

SET @currentrows = @@ ROWCOUNT

SET @currentrows = @@ROWCOUNT

END 

END 

希望有所帮助。

这篇关于改善DELETE查询的不良性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！