delete

[wuwang]

Hi,

I have more than 60,000 records need to delete.
My code is very simple -- delete table_name where id in (.....), but it ran very slow.

Is there any way to do it faster?
(I delete them by primary key)


Thank you

Paul

 

[tlbroadbent]

Deleting is generally slow because deletes are logged transactions. You can speed things up by locking the table before starting the delete. Add statements like the following before the delete statement.

Begin Transaction
--Lock table and hold lock to end of transaction
Select * From Table (with tablockx, holdlock)
Where 1=2

Delete...

Commit Transaction

Usually, it is faster to delete in multiple smaller batches and commit the transaction after each batch. This prevents the LOG from growing very large trying to hold all of the uncommitted deletes. Add Set Rowcount to the script to limit the number of records deleted in each transaction.

--Limit number of deletes to 10000
Set Rowcount 10000

<Delete script>

Set Rowcount 0 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.