我正在嘗試執行以下 T-SQL 陳述句：
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
DECLARE @nRows INT = 1
DECLARE @DataCancellazione DATE = DATEADD(DAY, -7, GETDATE())
CREATE TABLE #IDToDel (ID BIGINT)
WHILE @nRows > 0
BEGIN
INSERT INTO #IDToDel
SELECT TOP 5000 LogID
FROM MioDB.Test
WHERE CAST(ReceivedDate AS date) < @DataCancellazione
SELECT @nRows = @@ROWCOUNT
DELETE RM WITH (PAGLOCK)
FROM MioDB.Test RM WITH (PAGLOCK)
INNER JOIN #IDToDel TBD ON RM.LogID = TBD.ID
TRUNCATE TABLE #IDToDel
END
ROLLBACK
當我啟動執行時，查詢視窗似乎不再回應，并且行程中沒有特別增加 CPUTime 和 DiskIO。誰能幫我謝謝。
uj5u.com熱心網友回復：
老實說，我認為您將問題過于復雜化了。SQL Server 可以輕松地一次性處理數百萬行，我懷疑您可能會在幾 1M 行批處理中完成此操作。如果您要洗掉至少 4,000,000 行，那么在 5,000 行時，需要800次迭代。
也不需要臨時表， aDELETE可以使用 a TOP，因此您可以在每個回圈中洗掉那么多行。我用一個變數定義它，然后傳遞它（1,000,000 行）。這意味著在 4 次迭代中洗掉所有內容，而不是 800。您可能希望稍微減小大小，但我建議例如 500,000 很容易挑選。
這為您提供了以下更簡潔的批處理：
SET NOCOUNT ON;
--The following transaction level seems like a terrible idea when you're performing DDL statements. Don't, just don't.
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @BatchSize int = 1000000,
@DataCancellazione date = DATEADD(DAY, -7, GETDATE());
SELECT 1; --Dataset with 1 row
WHILE @@ROWCOUNT > 0
DELETE TOP (@BatchSize)
FROM MioDB.Test --A Schema called "MioDB" is a little confusing
WHERE ReceivedDate < @DataCancellazione; --Casting ReceivedDate would have had no change to the query
--And could well have slowed it down.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/460710.html