Thursday, July 1, 2010

Fast Delete !

Performance wise, a very fast delete can be achieved using a VIEW utilising an ORDER BY clause!!!

Create the view -
CREATE VIEW [dbo].[del_data] as
SELECT TOP(500) * FROM dbo.data WHERE id < 219150348 ORDER BY id
GO

Run a while loop to delete the data effeciently in batches -
WHILE(1=1)
BEGIN
DELETE dbo.del_data
IF @@ROWCOUNT < 500 BREAK
END

Link : SQLCAT - Fast ordered delete

No comments: