I have store procedure which delete data from sql server table. We store ticker wise data. Some ticker’s has high volume of data. Suppose for a ticker X which has 4 million data. When deleting 4 million data in batch then it takes lots of time.
Anyone Can have a look and tell me how to optimize this delete routine as a result 10 million data will be removing in few second.
Here is a snippet of my store procedure which is taking long time to delete 4 million data.
CREATE PROC USP_RemoveDataForCSMReport
(
@Ticker VARCHAR(20),
@Earning VARCHAR(10),
@PrePost VARCHAR(10)
)
AS
BEGIN TRY
DECLARE @r INT;
DECLARE @TickerName VARCHAR(20)
SET @TickerName=@Ticker
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP (100000) FROM tblOutputDetl_csmtuner where Ticker=@TickerName
SET @r = @@ROWCOUNT;
COMMIT TRANSACTION;
END
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP (100000) FROM tblQCComment_CSMTuner where Ticker=@TickerName
SET @r = @@ROWCOUNT;
COMMIT TRANSACTION;
END
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP (100000) FROM tblOutputDetl_Period_CSMTuner where Ticker=@TickerName
SET @r = @@ROWCOUNT;
COMMIT TRANSACTION;
END
Select * from tblearnings WHERE EarningTickerID IN
(
Select MAX(EarningTickerID) from tblearnings where Ticker=@TickerName and Earning=@Earning and PrePost=@PrePost
)
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Execution Plan Attached. 
Waiting for guide line to speed up high volume of data deletion operation. Thanks
