question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked EchoLiu-msft commented

Delete high volume of data and performance issue

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. 84903-11s.png Waiting for guide line to speed up high volume of data deletion operation. Thanks



sql-server-transact-sql
11s.png (510.1 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How many different tickers are in those tables?

0 Votes 0 ·

Do you have any update?

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

You are looping over the records 100,000 records at a time. Try changing that to 1,000,000.

You will need to experiment and determine the best batch size for your process.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

first time i was deleting all data at time then i face it was taking long time when there is million of data then i found a post in stackoverflow there they said delete high volume data in batch then i used batch technique.

can't we do some optimization at table level to speed up the delete operation when deal with huge data....is it not possible ?

0 Votes 0 ·

There is nothing really to optimize in your delete statement. The delete speed is highly dependent on your hardware.

1 Vote 1 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered EchoLiu-msft commented

Tom is wrong when he says that nothing can be optimized with a DELETE statement. Although, these plans look good, they are all index seeks. A common error with batching is that people don't have an index to support the batching, and therefore each iteration requires a scan.

Then again, that is only one of the tables.

One thing to consider though is there are other indexes on the table, beside the one that is used in the query and the clustered index. In that case, it may pay off to disable those indexes and rebuild once you are done.

And of course, you need to check for blocking - that could be the reason things are going slow. You should have the database for yourself for this type of operations.

Finally a small optimization with the WHILE statement. Initiate @r to 100000 and change it to:

WHILE @r = 1000000
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is not clear you said- One thing to consider though is there are other indexes on the table, beside the one that is used in the query and the clustered index. In that case, it may pay off to disable those indexes and rebuild once you are done. What other index i need to create. please guide with more details.

0 Votes 0 ·

I did not say that you should create any indexes.

Please keep in mind that I don't know details about your tables, and therefore what I say has to be on the speculative side of things.

From the plan I can tell that the table has a clustered index C, and a non-clustered index N. What I don't know is there are also non-clustered indexes, A, B and C. What I suggested was that if there are, you could disable them, and then rebuild them after the operation to speed up the operation.

It may be possible to take it one step further and run the batches over the clustered index so that also N can be disabled. Since I don't know the table, I can't say how that could be done.

0 Votes 0 ·

Thank you sir.

0 Votes 0 ·
Show more comments