Optimizing of stored procedure to improve perform

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Optimizing of stored procedure to improve perform

Author

Topic

rahulm_32003
Starting Member

7 Posts

Posted - 2012-09-04 : 00:26:37

I am new to SQLServer programming.Can some one guide me to rewrite the following procedure to improve its performance(reduce its execution time)

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
;

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-09-04 : 01:10:11

Have you tried to use SET SHOWPLAN_XML to view the execution plan? Within the plan , using the Results Grid, you'll see the Query Cost and the intended access path of the query. This may give you some clues . For example , Scans, Nested Loops etc.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

rahulm_32003
Starting Member

7 Posts

Posted - 2012-09-04 : 01:53:08

No I didn't use it yet.I will try it out.Can u plz tell me hot to delete the records in batch wise

quote:
Originally posted by jackv

Have you tried to use SET SHOWPLAN_XML to view the execution plan? Within the plan , using the Results Grid, you'll see the Query Cost and the intended access path of the query. This may give you some clues . For example , Scans, Nested Loops etc.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources