Luhar (9/13/2012)
I am not running it in transaction.No one will access the DB when this procedure runs as it runs nightly.
I just want to modify the procedure so as to run in it batches...for ex processing 10000 records at a time
How many rows are deleted, typically?
-- Recorded
--Retrieve the count of records to be deleted
SELECT @expected = COUNT(TRANS_ID) FROM TRANS
WHERE SCHEDULED = 1 AND PRACTICE_MODE = @boolean1
-- Used
DELETE FROM TRANS_ASSOCIATE WHERE TRANS_ID IN
(SELECT TRANS_ID
FROM TRANS
WHERE SCHEDULED = 1
AND ALLOWED = 1
AND PRACTICE_MODE = @boolean1)
Note that what you record and what you actually delete may be different.
As Tom stated, you may find some improvement by using a #temp table to hold the keys from table TRANS, as follows:
SET @totalExpected = @expected+@expected2
IF OBJECT_ID('tempdb..#Trans') IS NOT NULL DROP TABLE #Trans;
SELECT TRANS_ID
INTO #Trans
FROM TRANS
WHERE SCHEDULED = 1
AND ALLOWED = 1
AND PRACTICE_MODE = @boolean1
GROUP BY TRANS_ID
ORDER BY TRANS_ID;
CREATE UNIQUE CLUSTERED INDEX UCX_TRANS_ID ON #Trans (TRANS_ID);
IF (@totalExpected > 0)
BEGIN
DELETE FROM TRANS_ASSOCIATE WHERE TRANS_ID IN (SELECT TRANS_ID FROM #Trans)
--If there was an error then return right away
IF (@@ERROR <> 0) RETURN
DELETE FROM TRANS_LOCATION WHERE TRANS_ID IN (SELECT TRANS_ID FROM #Trans)
--If there was an error then return right away
IF (@@ERROR <> 0) RETURN
DELETE FROM TRANS_REBATE_XREF WHERE TRANS_ID IN (SELECT TRANS_ID FROM #Trans)
--If there was an error then return right away
IF (@@ERROR <> 0) RETURN
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden