How to delete a huge amount of data without increasing CPU on server?

I need to delete a huge amount of data without increasing CPU SQL server.

Here is example of my query. Subquery returns about 999K Rows and I need to delete one by one. But the problem it deletes first thousand and gives error

Msg -2, Level 11, State 0, Line 0
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

DECLARE @i INT
CREATE TABLE #TempListingTable (ID INT Primary Key IDENTITY(1,1), ListingID INT)
DECLARE @numrows INT
DECLARE @ListingID INT

INSERT #TempListingTable 

SELECT T1.ListingID
FROM Table T1 WITH(NOLOCK)
LEFT OUTER JOIN Table T2        
ON T1.ID = T2.ID
WHERE T1.ID IS NULL AND T1.ID IS NOT NULL

SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM #TempListingTable)
IF @numrows > 0
    WHILE (@i <= (SELECT MAX(ID) FROM #TempListingTable))
    BEGIN

    SET @ListingID = (SELECT ListingID FROM #TempListingTable WHERE ID = @i)    


    DELETE Listing WHERE ListingID = @ListingID

    SET @i = @i + 1

    END

If I delete in subquery 开发者_如何转开发like you can see below the CPU goes up and gives timeouts

DELETE T1
FROM Table T1 WITH(NOLOCK)
LEFT OUTER JOIN Table T2        
ON T1.ID = T2.ID
WHERE T1.ID IS NULL AND T1.ID IS NOT NULL

What would be the best approach in this case?

---

You need to fix your sql first, it should never delete any rows since T1.ID is null and T1.ID is not null. Fix that and use something like this

WHILE 1 = 1
BEGIN

    DELETE TOP 1000 T1  
    FROM Table T1 WITH(NOLOCK) 
    LEFT OUTER JOIN Table T2          
    ON T1.ID = T2.ID  
    WHERE 1 = 2
    -- replace 'where' statement with a prober wherestatement. 
    -- I assume this is the 'where' statement you want
    --WHERE T2.ID IS NULL 
    --AND T1.ID IS NOT NULL

    IF @@ROWCOUNT = 0 BREAK
END

---

That's a .NET timeout, and nothing to do with SQL Server. You don't need to increase the timeout, you need to batch the deletes. If you say it deletes the first thousand and then gives an error, take your script to do the deletes and throw it in a proc that limits the number of records to delete to 1000 by using either TOP 1000 or DELETE TOP(1000), and then have the application fire that proc repeatedly. Have the proc give a return value (0 if there are no more rows to delete, 1 if there are) to control the application firing the proc.

Otherwise, can you elaborate on why you need to delete almost 100K rows one at a time?