SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dual Column Partitioning


Dual Column Partitioning

Author
Message
bhushanvinay
bhushanvinay
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 196
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
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28571 Visits: 8986
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
bhushanvinay
bhushanvinay
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 196
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
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28571 Visits: 8986
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34158 Visits: 11359
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search