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

Dual Column Partitioning Expand / Collapse
Author
Message
Posted Friday, January 8, 2010 3:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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
Post #844190
Posted Friday, January 8, 2010 4:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:47 AM
Points: 7,005, Visits: 8,451
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #844206
Posted Friday, January 8, 2010 5:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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
Post #844213
Posted Friday, January 8, 2010 5:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:47 AM
Points: 7,005, Visits: 8,451
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #844215
Posted Saturday, January 9, 2010 8:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #844893
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse