July 2, 2014 at 8:41 am
Hi Team,
I've a large table in my SQL database with more than 10 million records, i want to clean the table every week by
deleting the records older than 30 days.
but for future analysis purpose i want the complete table, is there any possible way to keep the table as it is without effecting the current database performance.
Please suggest
July 2, 2014 at 11:14 am
Minnu (7/2/2014)
Hi Team,I've a large table in my SQL database with more than 10 million records, i want to clean the table every week by
deleting the records older than 30 days.
but for future analysis purpose i want the complete table, is there any possible way to keep the table as it is without effecting the current database performance.
Please suggest
You are contradicting yourself here. In one sentence you say you want to delete old data. In the next sentence you say you want to keep all the data. Which one is it? You could create an archive table and move your old data to the archive table. Then for analysis you could just join those two together.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2014 at 11:34 am
Also, the initial means of attacking this depend on how many rows of data make up the past 30 days. Once you have done the initial archive (which may be done differently than the weekly process), the number of rows to be archived will (or should be) greatly reduced as you would actually only be archiving a weeks worth of data each time since you want to archive data more than 30 days old each week.
July 2, 2014 at 2:40 pm
is there any possible way to keep the table as it is without effecting the current database performance.
Try clustering the table by datetime (rather than identity, I'm guessing) and specify the datetime in WHERE conditions for 30-day processing. That is, add a condition like this in the SELECT statements:
WHERE datetime_in_row >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 30, 0)
You could also rename the existing table, and create a view with the original table, adding that WHERE condition to the view.
When you need to see the entire history, you use a separate view or query.
OR: Yes, create an archive table, moving all 30+day data to it; this is very common and acceptable. Create a UNION ALL view of the 30-day table and the archive table when you need to see all data together.
OR: Another option is to partition your main table, assuming you have a version of SQL that supports partitioning.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply