Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How efficiently delete the old data? Expand / Collapse
Author
Message
Posted Saturday, March 29, 2014 1:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988

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
Post #1556193
Posted Saturday, March 29, 2014 3:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 1,703, Visits: 4,490
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_TO DATETIME = '2005-01-01';
BEGIN TRAN
SELECT TOP (@BATCHSIZE) *
--DELETE TOP (@BATCHSIZE) --
FROM [TBL_IMAGES]
WHERE ENROLLTIME < @DATE_TO;
COMMIT TRAN
GO 100

Post #1556197
Posted Saturday, March 29, 2014 4:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,057, Visits: 2,988
Thanks for provide that script..
Post #1556199
Posted Saturday, March 29, 2014 6:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 23,276, Visits: 31,996
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1556207
Posted Saturday, March 29, 2014 9:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 36,978, Visits: 31,498
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 recommendations 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1556260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse