July 28, 2009 at 8:36 am
I have a table with 107 Million rows in SQL2005. This table grows around 400, 000 rows everyday. I insert data from a flat file through SSIS (automatic process that runs every morning).
My SSIS process does:
- Read the Flat File (it’s size 35MB)
- Transform the data
- Insert data into a Bulk table
- Run a stored procedure to insert the data from the Bulk table and other join tables into the destination table (that one that has the 107 Million rows)
In order to improve performance in the insertion into the table, I’m planning to do a partition.
My question is:
What is the best partition I should apply to this case if I insert data every day?
Should I improve some process in my SSIS?
Regards,
July 29, 2009 at 6:03 pm
Hi
Obviously Partition Table will help with the fast loading of the data into your table.
In terms of how u design it, it all comes back to how you design your filegroup on which disk speed. As a rule of thumb, if you need to access information actively for information for the last 3 months, then I would normally put keep the last 3 months information on the fast disk filegroup and archive the older information to a slower disk file group.
Again, you need to think of how the usage of the data and balance that with the number of rows that you will need to insert on a daily basis. In terms of archiving, I would recommend to archive your data on nightly basis.
IT
August 2, 2009 at 6:18 am
If I read your post right, the only thing you will use partiotioning for is to switch the fresh data into the main "table" everymorning...
Am i right, nothing mentioned about deleting data ???
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply