September 13, 2012 at 3:26 am
Hi All
I have DB with 4 million records and a stored procedure which clears all the data from bunch of tables which are related with a primary key of Table T1 in 4 hrs
The delete procedure which I have is deleting all the records successfully in 4 hrs but it is utilizing huge amount of space.I just want to modify my stored procedure in order to perform deletion batch wise so that even if i kill the it will only rollback the changes to last commit.
I tried in different ways but it is taking more than 4 hrs.
Any ideas or suggestions are welcome.
CREATE procedure TRANS_DELETE @startDate1 datetime, @endDate1 datetime, @boolean1 numeric(1) as
--Variables to keep track of record counts
DECLARE @processed numeric(20)
DECLARE @expected numeric(20)
DECLARE @expected2 numeric(20)
DECLARE @totalExpected numeric(20)
DECLARE @notProcessed numeric(20)
--Initialize the record count variables
SET @processed = 0
SET @expected = 0
SET @expected2 = 0
SET @TotalExpected = 0
SET @notProcessed = 0
--Retrieve the count of records to be deleted
SELECT @expected = COUNT(TRANS_ID) FROM TRANS
WHERE SCHEDULED = 1 AND PRACTICE_MODE = @boolean1
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
SELECT @expected2 = COUNT(SECURITY_OVERRIDE_LOG_ID) FROM SECURITY_OVERRIDE_LOG
WHERE (TRANSACTION_NUMBER = '') AND (START_DATETIME >= @startDate1 AND START_DATETIME <= @endDate1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
SET @totalExpected = @expected+@expected2
IF (@totalExpected > 0)
BEGIN
DELETE FROM TRANS_ASSOCIATE WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS_LOCATION WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS_REBATE_XREF WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS_REBATE WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS_DISCOUNT_SPREAD WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS_DISCOUNT WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS_LINE_DISC WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS_LINE_PRICE WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS_LINE_TAX WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS_LINE_ASSOCIATE WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS_CUSTOMER WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1)
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
DELETE FROM TRANS WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1
--If there was an error then return right away
IF (@@ERROR <> 0)
BEGIN
RETURN
END
SET @processed = @totalExpected - @notProcessed
SELECT @processed AS PROCESSED, @totalExpected AS EXPECTED
September 13, 2012 at 2:42 pm
1) Are you running the stored proc in a transaction, as you mentioned you want to be able to rollback. If you do SQL Server will be very slow deleting a large number of records, and will keep locking more and more of the tables as it deletes. There are some strategies for getting around this (without running large deletes in a transaction), such as using an IsDeleted column, moving records to a Deleted Table - but if it goes wrong, you need code to recover, as obviously you won't have rollback as an option.
2) are there any triggers on any of the tables you delete from? these could also slow down your code.
3) look for some optimisations.
It looks like your using WHERE TRANS_ID IN
(SELECT TRANS_ID FROM TRANS
WHERE SCHEDULED = 1 AND ALLOWED = 1 AND PRACTICE_MODE = @boolean1) in a number of places. Perhaps select those TRANS_IDs into a #Tmp table, index it on Trans_ID and join the #tmp to the table you're deleting from instead of accessing the TRANS table for every delete.
4) are users accessing the tables at the time you run the delete, or do you run it in a maintenance time?
September 13, 2012 at 11:35 pm
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
September 14, 2012 at 3:12 am
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
September 14, 2012 at 3:41 am
we have 4 million records in our test db
How to delete them batch wise....Can you gimme a hint
September 14, 2012 at 4:13 am
Luhar (9/14/2012)
we have 4 million records in our test dbHow to delete them batch wise....Can you gimme a hint
Here's a useful script which Lynn Pettis prepared earlier: http://www.sqlservercentral.com/Forums/FindPost1357526.aspx
You have 4 million rows in your test db which classifies it as "very small", but of those, how many rows do you currently delete?
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply