Stored procedure performance improvement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Stored procedure performance improvement

Stored procedure performance improvement

Rate Topic

Display Mode

Topic Options

Author

Message

Luhar

Posted Thursday, September 13, 2012 3:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 04, 2012 11:48 PM
Points: 4, Visits: 28

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

Post #1358427

Tom Brown

Posted Thursday, September 13, 2012 2:42 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:16 AM
Points: 1,196, Visits: 1,319

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?

Post #1358882

Luhar

Posted Thursday, September 13, 2012 11:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 04, 2012 11:48 PM
Points: 4, Visits: 28

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

Post #1359049

ChrisM@Work

Posted Friday, September 14, 2012 3:12 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 5,618, Visits: 10,990

Luhar (9/13/2012)

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1359127

Luhar

Posted Friday, September 14, 2012 3:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 04, 2012 11:48 PM
Points: 4, Visits: 28

we have 4 million records in our test db
How to delete them batch wise....Can you gimme a hint

Post #1359134

ChrisM@Work

Posted Friday, September 14, 2012 4:13 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 5,618, Visits: 10,990

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}

Post #1359153

« Prev Topic | Next Topic »

Permissions