Partition Order_Products line table based on date in Order header table

Question

Partition Order_Products line table based on date in Order header table

JayK

SSCrazy

Points: 2679
More actions
December 12, 2010 at 5:18 pm

#388420

Hi,
I have an Order_Products table which has all products for all orders in our database for the past 5 years. The Order_Products table is transactionally replicated on our reporting server from our production server.
What I would like to do for reporting purposes is partition the Order_Products table to contain only the past 3 months of order_Products (based on join with Orders table) in 1 partition and the rest of the data in the other partition.
Therefore when running reports a query will only be made on the partition with the last 3 months worth of data.
Does this seem doable? The partitioned order_products would still need to be replicated from the production server as its constantly being updated.
Thanks for any tips / advice. I have not attempted anything like this before!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63421 More actions · Answer 1

JayK (12/12/2010)
Hi,
I have an Order_Products table which has all products for all orders in our database for the past 5 years. The Order_Products table is transactionally replicated on our reporting server from our production server.
What I would like to do for reporting purposes is partition the Order_Products table to contain only the past 3 months of order_Products (based on join with Orders table) in 1 partition and the rest of the data in the other partition.
Therefore when running reports a query will only be made on the partition with the last 3 months worth of data.
Does this seem doable? The partitioned order_products would still need to be replicated from the production server as its constantly being updated.
Thanks for any tips / advice. I have not attempted anything like this before!

You might need to denormalize a little bit and include the OrderDate from the OrderHeader table in the OrderDetails table and then partition on the OrderDate in the OrderDetails table. You could easily add a trigger to the OrderHeader table or add the code to the stored procedure that adds the detail records to the table OrderDetails table.