Stored procedure performance improvement

Question

Stored procedure performance improvement

Luhar

SSC Enthusiast

Points: 188
More actions
September 13, 2012 at 3:26 am

#98731

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

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?

Luhar SSC Enthusiast Points: 188 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Luhar SSC Enthusiast Points: 188 More actions · Answer 4

we have 4 million records in our test db

How to delete them batch wise....Can you gimme a hint

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Luhar (9/14/2012)
we have 4 million records in our test db
How 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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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