Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedure performance improvement Expand / Collapse
Author
Message
Posted Thursday, September 13, 2012 3:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, September 13, 2012 2:42 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 1,268, Visits: 1,397

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
Posted Thursday, September 13, 2012 11:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, September 14, 2012 3:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 6,748, Visits: 12,843
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1359127
Posted Friday, September 14, 2012 3:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, September 14, 2012 4:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 6,748, Visits: 12,843
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1359153
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse