CODEWITHSUNDEEP
sql-server
Danish
Danish

Reputation: 21

Improve UPDATE/DELETE performance SQL Server 2012

We have process in our project where records in a table with specific flag is deleted and remaining record's flag is updated.

Table have approx 45 million records and half the records are with flag='C' and remaining half with flag='P'.

Process run once in a day to delete all the records with flag 'P' and then update all the remaining ones with flag 'C'

Below are the two statements that is run through SSIS package.

DELETE FROM dbo.RTL_Valuation WITH (TABLOCK) 
WHERE Valuation_Age_Flag = 'P';

UPDATE dbo.RTL_Valuation WITH (TABLOCK) 
SET Valuation_Age_Flag = 'P' 
WHERE Valuation_Age_Flag = 'C';

Currently process takes 60 minutes to complete. Is there any way process time could be improved ?

Thanks

Upvotes: 1

Views: 3436

Answers (2)

Martin Karouš
Martin Karouš

Reputation: 153

Ok. I assume, that when you perform your delete and update statements it results into two scans of the entire table (one to identify the rows to delete and one to identify the rows to update) and then you have to perform fully logged delete and update operations over it.

There is nice trick for situations like this if your database is in the simple recovery model. However, whether this is suitable for you depends on other circumstances (e.g. how many indexes you table has, whether there are some references, data types ...) that I am not able to asses from your description. It requires more coding but it usually results into much better performance. You would have to test whether it works better for you than your original approach.

Anyway, the trick works like this:

  1. Instead of delete and update operations just select the rows you want to keep (including the changes of the flag) using "SELECT INTO" construct into new table. This results in the minimally logged insert operation and single table scan. You can use also the "INSERT INTO SELECT" construct but there you must fulfill some additional conditions to get the minimally logged insert.
  2. Once data is in the new table, you have to build all required indexes on it.
  3. Once all indexes are build, you just truncate the original table and using the SWITCH command you simply switch the data back to the original table and drop the "new table". It works also on the standard edition of the SQL Server.

Upvotes: 0

benjamin moskovits
benjamin moskovits

Reputation: 5458

You need to do 10000 rows at a time. You are creating one enormous transaction that takes up a lot of room in the transaction log (so it can be rolled back).

     set nocount on
      DELETE  top (10000) FROM dbo.RTL_Valuation   WHERE valuation_Age_Flag = 'P';    
    while @@rowcount()>0

    begin
     DELETE  top (10000) FROM dbo.RTL_Valuation   WHERE valuation_Age_Flag = 'P';    
end

You can try 1,000, 5,000 or some other number to determine which is the best 'magic' number to quickly delete rows from a large table on your install of SQL Server. But it will be a lot faster that doing a big delete. The same logic applies to the update.

Upvotes: 1

Related Questions