January 8, 2010 at 3:50 am
Need Help With Partitioning.
I have a Table
Create Table OrdersFromSystemsFeedTable
(
InternalOrderID,
SystemOrderID,
ExternalSystemName,
OrderDate,
IsProcessed,
)
Need to remove data which is 3 months old, Huh!! Dodle, easy to do this, Catch !! how to make sure that the data which is not processed which is older than 3 months is not archived via Paritioning.
Is there a way this can be done or I am barking the wrong tree.
Kind regards
Vinay
Feed Table is huge 30M Records and 5M Each month growth Stats.
Regards
Vinay
January 8, 2010 at 4:43 am
the query will provide the same results. partitioned or not.
If you are refering to the sliding window technique, off course, you should first check it the partition only contains data to be removed before doing it to a full partition.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2010 at 5:10 am
Let me Re - query the same,
The idea is to have 3 paritions for each month when we hit the limit then remove the last partition, Issue here is when we do that we dont want the Isprocessed Flag rows to be gone they should be still in the DB and available online.
Sliding window is what i am looking for But the Partition function can be done only on one column AFAIK.
Is there any Stratergy? which you guys are already using.
I can write a proc for purge, This table is a aggregation of different Orders from different vendors.
That gives a lot more control but its not fast as Sliding window Archiving process. How to do a Sliding window with conditional data? is there a way to do this.
Regards
Vinay
January 8, 2010 at 5:35 am
Don't shoot me if it doesn't perform as expected.
You could add a persisted derived column and partition on the combination of the flagprocessed and the stringconverted datetime combined..
Keep min mind this will shift rows from one partition to another whenever the flagprocessed is modified !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 9, 2010 at 8:25 am
1. Using a (persisted) computed column would be my preference also. If the date portion is placed first in the compound field, you could arrange the partition function so that changing the processed status only results in a change of partition for rows older than 3 months. You would then just not archive partitions in the pre-3-month range which hold unprocessed rows. To minimize data movement when crossing the 3-month boundary, ensure that processed rows stay in the same partition as they age.
2. Assuming that there are relatively few unprocessed rows older than 3 months, another strategy might be to temporarily move the unprocessed rows older than 3 months to a temporary table or variable just before the partition is archived, and then re-insert them afterward.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply