none
deleting data from 200 tables for particular column values RRS feed

  • Question

  • Hello All,

    I have to delete the unwanted records from all the 200 tables considering two columns of dbo4.id_trans_map table as my reference.  Below is the code I have written for this, I have not added delete to this, as first I want to ensure that the code is right.  There will around 200X1000 delete  statements, what would be good approach to run this.

    Below is the sample code I have started with, can you help me with correcting this or validating 

    DECLARE @bt_id NVARCHAR(100)
    DECLARE @vtrans_id NVARCHAR(100)
    DECLARE @vTabName NVARCHAR(300)
    DECLARE @vSQL NVARCHAR(1000)
    
    --list down all the batches and trans_ids(around 10K rec here) where ref_no is null and delete these recs from 200+ tables
    DECLARE curEmpiRfmNull CURSOR
    FOR SELECT bt_id,trans_id FROM dbo4.id_trans_map WHERE ISNULL(rf_no,'')=''
    
    --list of all the 200+ tables from where I need to delete the rec pertaining to above cursor condition
    DECLARE dboTabList CURSOR
    FOR SELECT  'dbo.' + T.TABLE_NAME  
    FROM  dbo4.INFORMATION_SCHEMA.TABLES T
    WHERE T.TABLE_NAME IN (SELECT  TABLE_NAME 
    FROM dbo4.INFORMATION_SCHEMA.COLUMNS C 
    WHERE  C.COLUMN_NAME  IN ('bt_id')  -- only tables which has bt_id to be picked
      )
    OPEN curEmpiRfmNull;
    FETCH NEXT FROM curEmpiRfmNull INTO  @bt_id,@vtrans_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
    OPEN dboTabList;
    FETCH NEXT FROM dboTabList INTO @vTabName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT @vSQL= 'DELETE '+@vTabName+' WHERE bt_id='''+@bt_id+''' AND trans_id ='''+@vtrans_id +''''-- building sql for this purpose 
    
    
    PRINT @vSQL
    FETCH NEXT FROM dboTabList INTO @vTabName
    END
    CLOSE dboTabList;
    FETCH NEXT FROM curEmpiRfmNull INTO  @bt_id,@vtrans_id
    END
    
    CLOSE curEmpiRfmNull;
    CLOSE dboTabList; 



    Neil









    • Edited by Neilcse Thursday, October 24, 2019 9:50 PM
    Thursday, October 24, 2019 9:15 PM

Answers

  • I don't know what tuning I can do here as any way I have to run those many delete stms, either in dynamic code or independently.

    No, you don't have to run that many DELETE statements, if you delete many rows at a time.

    Having a loop, so that you can delete rows in chunks, although with the volumes you indicated, I am not sure it is needed. (But it all depends on how big the rows are in bytes.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Neilcse Monday, October 28, 2019 3:45 AM
    Friday, October 25, 2019 9:47 PM
    Moderator
  • I dropped the earlier code pasted in previous posts and updated the code like below and it is running much better and quickly it ran in 5min, the one above for running for hours

    DECLARE @vTabName NVARCHAR(300)
    DECLARE @vSQL NVARCHAR(1000)
    DECLARE dboTabList CURSOR
    FOR SELECT tabname 	FROM  diobj.dbo.dboTabList WHERE Cleaned= 'No'
    
    OPEN dboTabList
    FETCH NEXT FROM dboTabList INTO @vTabName
    WHILE @@FETCH_STATUS = 0
    	BEGIN
    
    		SELECT @vSQL = 'DELETE FROM dbo4.ids.'+ @vTabName+ ' WHERE EXISTS 
    		(SELECT 1 FROM dbo4.ids.id_trans_map b
    		WHERE b.bt_id = dbo4.ids.'+ @vTabName+'.bt_id and b.trans_id = dbo4.ids.'+@vTabName+'.trans_id and ISNULL(rf_no,'''')='''')'	
    		EXEC (@vSQL)
    		UPDATE diobj.dbo.dboTabList SET Cleaned= 'Yes' WHERE tabName = @vTabName
    	FETCH NEXT FROM dboTabList INTO @vTabName
    END 
    CLOSE dboTabList
    DEALLOCATE dboTabList


    Neil


    • Edited by Neilcse Saturday, October 26, 2019 4:58 AM
    • Marked as answer by Neilcse Monday, October 28, 2019 3:46 AM
    Saturday, October 26, 2019 3:32 AM

All replies

  • How many rows are you looking into to delete from the main table? Deleting them one by one will take a loooooooong time. Get all ids to delete in a temp table and use that one in your queries.

    If you would still use this code, this statement:

    SELECT @vSQL= 'DELETE '+@vTabName+' WHERE bt_id='+@bt_id+' AND trans_id ='+@vtrans_id
    Should be
    SELECT @vSQL = 'DELETE ' + quotename(@vTabName) + ' WHERE bt_id = @bt_id AND trans_id = @trans_id'

    Which you run this way:

    EXEC sp_executesql @vSQL, N'@bt_id nvarchar(100), @trans_id nvarchar(100)'. @bt_id, @trans_id

    Always use parameters when you can!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 24, 2019 9:56 PM
    Moderator
  • The table the cursor curEmpiRfmNull will have 10K rows and the list of all the tables from which I have to delete is around 150 i,e. cursor  dboTabList, I am looking to store this list in a table now and mark it as complete once deleted so that if it fails I can restart from there. 

    Just a simple physical table having 2 column 1 table name other status. 


    Neil

    Thursday, October 24, 2019 11:15 PM
  • Using Cursor in such a situation will be very slow I think. Also just ask other developer to review your code without any testing material is not very practical.


    Friday, October 25, 2019 9:02 AM
  • I just changed the inner loop to outer and outer to inner and added a table with status column to track, how many completed. if I have to restart. I don't know what tuning I can do here as any way I have to run those many delete stms, either in dynamic code or independently.

    It ran around 70K stm over night and failed in between due to network, good that I added table which track and can restart from the point of failure.

     

    Neil

    Friday, October 25, 2019 1:53 PM
  • I don't know what tuning I can do here as any way I have to run those many delete stms, either in dynamic code or independently.

    No, you don't have to run that many DELETE statements, if you delete many rows at a time.

    Having a loop, so that you can delete rows in chunks, although with the volumes you indicated, I am not sure it is needed. (But it all depends on how big the rows are in bytes.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Neilcse Monday, October 28, 2019 3:45 AM
    Friday, October 25, 2019 9:47 PM
    Moderator
  • for some tables we can skip but need to identify them, some tables have millions and other have no rec or few rec.

    No, you don't have to run that many DELETE statements, if you delete many rows at a time.

    I didn't get this?

    Planning to add check to see if data exist then delete else move on to next .. 

    What would be better select top 1 or count(*)?


    Neil


    • Edited by Neilcse Saturday, October 26, 2019 12:21 AM
    Friday, October 25, 2019 11:56 PM
  • I dropped the earlier code pasted in previous posts and updated the code like below and it is running much better and quickly it ran in 5min, the one above for running for hours

    DECLARE @vTabName NVARCHAR(300)
    DECLARE @vSQL NVARCHAR(1000)
    DECLARE dboTabList CURSOR
    FOR SELECT tabname 	FROM  diobj.dbo.dboTabList WHERE Cleaned= 'No'
    
    OPEN dboTabList
    FETCH NEXT FROM dboTabList INTO @vTabName
    WHILE @@FETCH_STATUS = 0
    	BEGIN
    
    		SELECT @vSQL = 'DELETE FROM dbo4.ids.'+ @vTabName+ ' WHERE EXISTS 
    		(SELECT 1 FROM dbo4.ids.id_trans_map b
    		WHERE b.bt_id = dbo4.ids.'+ @vTabName+'.bt_id and b.trans_id = dbo4.ids.'+@vTabName+'.trans_id and ISNULL(rf_no,'''')='''')'	
    		EXEC (@vSQL)
    		UPDATE diobj.dbo.dboTabList SET Cleaned= 'Yes' WHERE tabName = @vTabName
    	FETCH NEXT FROM dboTabList INTO @vTabName
    END 
    CLOSE dboTabList
    DEALLOCATE dboTabList


    Neil


    • Edited by Neilcse Saturday, October 26, 2019 4:58 AM
    • Marked as answer by Neilcse Monday, October 28, 2019 3:46 AM
    Saturday, October 26, 2019 3:32 AM
  • No, you don't have to run that many DELETE statements, if you delete many rows at a time.

    I didn't get this?

    I was referring exactly to what you posted a few hours later:

    DECLARE @vTabName NVARCHAR(300)
    DECLARE @vSQL NVARCHAR(1000)
    DECLARE dboTabList CURSOR
    FOR SELECT tabname    FROM  diobj.dbo.dboTabList WHERE Cleaned= 'No'

    OPEN dboTabList
    FETCH NEXT FROM dboTabList INTO @vTabName
    WHILE @@FETCH_STATUS = 0
       BEGIN

          SELECT @vSQL = 'DELETE FROM dbo4.ids.'+ @vTabName+ ' WHERE EXISTS       (SELECT 1 FROM dbo4.ids.id_trans_map b
          WHERE b.bt_id = dbo4.ids.'+ @vTabName+'.bt_id and b.trans_id = dbo4.ids.'+@vTabName+'.trans_id and ISNULL(rf_no,'''')='''')'         EXEC (@vSQL)
          UPDATE diobj.dbo.dboTabList SET Cleaned= 'Yes' WHERE tabName = @vTabName
       FETCH NEXT FROM dboTabList INTO @vTabName
    END CLOSE dboTabList
    DEALLOCATE dboTabList

    Good work!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, October 26, 2019 8:41 AM
    Moderator
  • Thank you Erland,Dedmon and all for your contribution. 

    Getting Good work! note from Erland is an achievement for me! 

    Regards,

    Neil


    Neil

    Monday, October 28, 2019 3:47 AM