How to delete records from a large table in small portions

At times, you need to delete records from a large table. Performing a delete operation on such a table can become quiet resource intensive. In order to increase performance, you can delete the rows in small portions. Let us see how using this query:


-- Create a temporary table CustomersTemp
SELECT * into Northwind.dbo.CustomersTemp from
Northwind.dbo.Customers

-- Procedure to delete in small groups
CREATE PROC spoc_deleteinsmallgroups
@NoOfRowsToDelete int
AS
DECLARE @CurrentRowCount int

-- Count the number of rows in the table
SELECT @CurrentRowCount = (SELECT COUNT(*) FROM Northwind.dbo.CustomersTemp)

-- Loop and delete records in small bits till the rowcount is 0
WHILE(@CurrentRowCount > 0)
BEGIN
DELETE TOP (@NoOfRowsToDelete) FROM Northwind.dbo.CustomersTemp
-- RECOUNT
SELECT @CurrentRowCount = (SELECT COUNT(*) FROM Northwind.dbo.CustomersTemp)
Print @CurrentRowCount
END

-- Execute the procedure and pass the number of rows
-- to delete at a time
EXEC spoc_deleteinsmallgroups 25


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

12 comments:

Unknown said...

Huh? Is this SQL Server? You are deleting all the rows, right? Doesn't SQL server have a truncate command?

Just drop the table and rebuild it.

Suprotim Agarwal said...

Hi,

Thanks for your comment Eric. What if the user does not want to delete all the rows..just few rows that match a condition. (Eg: 50k rows in a table containing 2 lakh rows).

Gowrisankar M said...

Good work Agarwal,

A small suggestion to it.

Count(*) will really be time consuming if the size is too much. Instead you can use the following query to get the total row count.

select rowcnt
from sysindexes
where id = object_id('customers')
and indid = 1

Suprotim Agarwal said...

Thanks thedreamer,

That's a good suggestion. I will try it out and check the difference in the time taken.

Anonymous said...

Getting count for every iteration inside the While loop is a waste of resources. Just Check the @@RowCout after delete and Break out of loop. Here is a better version of it...
While (1=1)
Begin
Delete Top X from Table
If @@rowcount = Break Else Continue
End

Anonymous said...

preddy..did you try it on a larger table with more than 100000 records..I think agarwal's query performs better over tehre..

Anonymous said...

pouah! try it on a table where you've got more than 190 Millions of rows ! when you knows that a simple select take 6 hours...just use the top, and then create a job

Anonymous said...

the Dreamer's idea to get rowcount is cool.

I have also mentioned the same in my blog
http://techcreeze.blogspot.com

Anonymous said...

Hi the same delete qry is giving error saying Incorrect syntax near top. Has anybody actually tried this? Top count works with delete command???

Suprotim Agarwal said...

Anonymous: It does work well. Please select the database as Northwind before trying it.

Anonymous said...

Does this work with SQL Server 2000? I am getting the same error as the last guy.

Alex said...

Your code snippet deletes the records from the temporary table. What is the purpose behind it?