Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Efficient Data Deletion Method and Transactional Databases


Efficient Data Deletion Method and Transactional Databases

Author
Message
Babar Javaid
Babar Javaid
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 78
What is the efficient method/T-SQL that I can use to delete data from my SQL Server 2008 R2 database.

Currently I am using

DELETE TOP (100) PERCENT FROM Table1
WHERE Id IN
(
SELECT Id FROM DeletionIds
)



As I have to delete data from 20 tables so first I move the choosen Ids into DeletionIds table and then delete all these ids from referenced key tables.

If I am doing something wrong; kindly guide as data deletion process is becoming very critical.

We have disk space limitations and currently cannot increase it.

The daily data insert operations are from 5-7 millions and currently I am only able to delete 2.5-3 million records from database. It means incoming data is more than outgoing data and incoming data is eating my disk space.

Please help to resolve this issue; Thank you.
Dev
Dev
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1944 Visits: 1598
As a quick suggestion you may modify your code to match with following...

Example:

-- JOIN based deletion
USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;




Now few question:
• Why TOP (100) PERCENT?
• What’s the purpose of mass deletion? Please explain it. We might be able to suggest better.

Dev

*** Open Network for Database Professionals ***
http://www.linkedin.com/in/devendrashirbad

MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
The most efficient way to do mass deletions is to introduce a sliding window partitioned table setup.

This would allow you to switch out whole days of data with out meta data changes and allow you to drop the table you switched the partition into.
okbangas
okbangas
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 1387
First of all, I agree with Dev on the syntax. So, to your issue. If you have referential integrity, you have to delete from the referencinig tables first, followed by deleting from the referenced (central) table. How simple this may look, you may run into performance issues, most likely with the last delete. When you delete from the central table, referential integrity will be evaluated against all the referencing tables, possibly creating some 20 table or clustered index scans in your execution plan. If so, you may consider creating nonclustered indexes on the column(s) in the referencing tables which is a part of your foreign keys. I hope this helps.

As for space, deleting from tables do generate a lot of transaction log, beacuse SQL Server both logs the deletion and the old data so that the change can be rolled back, and it reserves enough space in the transaction log to be able to roll back. It is not uncommon for me to generate 10GB+ of transaction log for a delete transaction (from some ten tables).



Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL
Babar Javaid
Babar Javaid
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 78
I worked with Join previously and found that It takes around 15-20 min to delete 1 million records from 20 tables.
Then I changed the strategy and first pick and insert the ids (primary key) in DeletionIds table and then delete all foreign keys data (19 tables) first and then remove the data from main table and deletion time reduced to 10-13 minutes.

Why TOP (100) PERCENT?
I just do some R & D on web and try to find quick way to delete data from database. With this I found a 20-30% improvement in deletion process as compared to DELETE FROM Table1.

Mass Deletion?
1. The main reason is that my client's requirement is that I have to delete the data from database older than specific retention period (say 30 days)
2. Also we have a limited disk space that we want to use for new/latest data and new client for the application.

@MysteryJimbo; I will look into "sliding window partitioned table setup". Does this support all SQL 2008 R2 versions? I am using Standard version of SQL Server.

Thanks
Dev
Dev
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1944 Visits: 1598
Babar Javaid (12/20/2011)
I worked with Join previously and found that It takes around 15-20 min to delete 1 million records from 20 tables.
Then I changed the strategy and first pick and insert the ids (primary key) in DeletionIds table and then delete all foreign keys data (19 tables) first and then remove the data from main table and deletion time reduced to 10-13 minutes.

Why TOP (100) PERCENT?
I just do some R & D on web and try to find quick way to delete data from database. With this I found a 20-30% improvement in deletion process as compared to DELETE FROM Table1.

Mass Deletion?
1. The main reason is that my client's requirement is that I have to delete the data from database older than specific retention period (say 30 days)
2. Also we have a limited disk space that we want to use for new/latest data and new client for the application.

@MysteryJimbo; I will look into "sliding window partitioned table setup". Does this support all SQL 2008 R2 versions? I am using Standard version of SQL Server.

Thanks


Based on your reply, 'sliding window' is a good idea. Yes, SQL Server 2008 R2 supports it.

Dev

*** Open Network for Database Professionals ***
http://www.linkedin.com/in/devendrashirbad

Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
Babar Javaid (12/20/2011)
1. The main reason is that my client's requirement is that I have to delete the data from database older than specific retention period (say 30 days)

IF this would be a continuous periodic job (say every month) i would recommend Batch approach in off peak hours, by this you can also manage you log.

Babar Javaid (12/20/2011)
2. Also we have a limited disk space that we want to use for new/latest data and new client for the application.
rather deleting the data , i would say move the required data (latest) data into permanent tmp table (prefer new disk if you can or if you require) , rename existing table into some history table, and rename tmp into main table.in this way you can also put less overhead (log's space wise) on main table.and less data to operate.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
apatel62
apatel62
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 22
Deleting as fast as possible:
Two suggestions 1) log(ldf) and data(mdf) files on physically separate disks.
2) Partition the table so that records to be deleted are distributed among several spindles
if possible, partition the table so that incoming data and to_be_deleted data resides on seperate partition. if you can do that just delete the partition.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
apatel62 (11/7/2012)
Deleting as fast as possible:
Two suggestions 1) log(ldf) and data(mdf) files on physically separate disks.
2) Partition the table so that records to be deleted are distributed among several spindles
if possible, partition the table so that incoming data and to_be_deleted data resides on seperate partition. if you can do that just delete the partition.
+1

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search