December 10, 2009 at 2:14 pm
SCENARIO:
Two tables, always used in a join together, with a 1:N relationship. Table 1 grows by about 100K rows a day, table 2 grows by about 2.5M rows a day.
Both tables are queried in MONTHLY "chunks" based on a single column (a DATE type column).
Data is BULK INSERTED daily....its not OLTP....its just "batch" inserts daily.
Since the data for a single day comes in multiple batches, even if I used DAILY partitions, I could not use SWITCH to do the inserts....because I am "adding" to an existing partition with data, NOT adding to an empty partition.
QUESTIONS:
1) Is partitioning worth it? I think so. I would partition RIGHT with the first day of each month as the boundary, giving me one partition per month on each table, and I would use the same partition column on both tables. COMMENTS?
2) Is it OK for me to create the partition function with the next 10 YEARS WORTH OF PARTITIONS to minimize maintenance of the function? Is it a BIG problem to have so many EMPTY partitions in advance? I would insert into the 200912 partition this month and 201001 next month... but I won't get to the 202001 partition for 10 years. Is that a problem? COMMENTS?
I welcome your input.
Thank you.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply