CODEWITHSUNDEEP
sqlsql-server
Andy
Andy

Reputation: 85

Most efficient way to delete records from a huge table

I have a table tblcalldatastore which produce around 4000000 records daily. I want to create a daily job to delete any record order than 24 hours. What is the most efficient and less time taking way? Below query is my requirement.

 delete from [tblcalldatastore] 
 where  istestcase=0      
 and datediff(hour,receiveddate,GETDATE())>24

Upvotes: 1

Views: 2849

Answers (2)

john McTighe
john McTighe

Reputation: 1181

You might not want to go down the Partitions route.

It looks like you will typically be deleting approx half the data in your table every day.

Deletes are very expensive... A much faster way to do this is to

Select INTO a New Table (the data you want to keep)

rename (or Drop) your old Table

Then Rename your new table to the old table name.

This should work out quicker - Unless you have heaps of Indexes & FKs...

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The better approach is to avoid delete entirely by using partitions on your table. Instead of deleting records, drop partitions.

For example, you can create a partition for each hour. Then you can drop the entire partition for the 25th hour in the past. Or you can basically have two partitions by day and drop the older one after 24 hours.

This approach has a big performance advantage, because partition drops are not logged at the record level, saving lots of time. They also do not invoke triggers or other checks, saving more effort.

The documentation on partitioning is here.

Upvotes: 3

Related Questions