Query Session no longer respond-CodePudding

I'm trying to execute the following T-SQL Statement:

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

When I launch the execution the query window seems to no longer respond and without having particular increase of CPUTime and DiskIO on the process. Can anyone help me thanks.

CodePudding user response：

Honestly, I think you're overly complicating the problem. SQL Server can easily handle processing millions of rows in one go, and I suspect that you could likely do this in a few 1M row batches. If you have at least 4,000,000 rows you want to delete, then at 5,000 a batch that will take 800 iterations.

There is also no need for the temporary table, a DELETE can make use of a TOP, so you can just delete that many rows each cycle. I define this with a variable, and then pass that (1,000,000 rows). This would mean everything is deleted in 4 iterations, not 800. You may want to reduce the size a little, but I would suggest that 500,000 is easy pickings for instance.

This gives you the following more succinct batch:

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.