How efficiently delete the old data?

Question

How efficiently delete the old data?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
March 29, 2014 at 1:13 am

#305833

How efficiently delete old data for purging older than last 5 years data
Selected and verified last 5 years data from as below command
SELECT * FROM TBL_IMAGES where ENROLLTIME <= DATEADD (year,-5, GETDATE()) order by ENROLLTIME desc
(2265459 row(s) affected)
Data available for last 5 years (2005 to 2010)
Result will display after 46 minutes
For the above statistics, I cannot run DELETE command as below, so database will be hanging during deleting last 5 years data.
DELETE FROM TBL_IMAGES where ENROLLTIME <= DATEADD (year,-5, GETDATE()) order by ENROLLTIME desc
Pls. suggestion me, If any best method?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

ananda.murugesan (3/29/2014)
DELETE FROM TBL_IMAGES where ENROLLTIME <= DATEADD (year,-5, GETDATE()) order by ENROLLTIME desc
Pls. suggestion me, If any best method?

Here is a method that I have used for one off purging in SSMS / sqlcmd.

The @BATCHSIZE variable should be set to a manageable number, the

GO N is the number of executions loops.

/***************************************

Delete @BATCHSIZE number of records in SSMS/sqlcmd

***************************************/

DECLARE @BATCHSIZE INT = 100;

DECLARE @DATE_TODATETIME = '2005-01-01';

BEGIN TRAN

SELECT TOP (@BATCHSIZE) *

--DELETE TOP (@BATCHSIZE) --

FROM [TBL_IMAGES]

WHERE ENROLLTIME < @DATE_TO;

COMMIT TRAN

GO 100

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

SQL Galaxy

One Orange Chip

Points: 29472

March 29, 2014 at 4:29 am

#1701356

Thanks for provide that script..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

What is the recovery model on the database in question? If you need to control the growth of your transaction log, you may want to consider another alternative.

My I suggest reading the following article: [/url=http://www.sqlservercentral.com/articles/T-SQL/67898/]Deleting Large Number of Records[/url].

You may also want to read the discussion that accompanies the article.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 4

ananda.murugesan (3/29/2014)
How efficiently delete old data for purging older than last 5 years data

Batch Delete Loops aren't "efficient". Large singleton deletes aren't "efficient". Deletes in general, aren't "efficient". Partitioning tables just to do a one time delete is not "efficient" but might be worthwhile if you need to do deletes in the future.

It sounds like you're trying to delete a whole lot more from the table than what will remain. In order to make a good recommendation for your situation, I'd need to know how many rows the table has in it now, how many rows you actually need to delete, what the table looks like (Create Table statement with ALL the bells and whistles including indexes, keys, FKs, constraints, etc), and whether or not older rows are updated or not. For example, once rows are inserted into an Audit table, they are never updated. An invoice detail table might have rows updated for a certain period of time. Other tables may have rows updated across vast ranges of time. I need to know what the rows will go through to make a decent recommendation.

In fact, it might be recommendation[font="Arial Black"]s[/font] depending on the way the rows in the table are used and whether or not you want to be able to do "efficient" deletes in the future.

And, to be sure, doing deletes to solve the problem of deleting large numbers of rows is probably the wrong way to do it in this case. There are better ways. I just need the info I asked for above to help decide which method to use.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)