Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
veggiop
Starting Member
10 Posts |
 Posted - 2011-04-07 : 05:38:36
|
| I am looking for some suggestions. We have a very large table.. let' call it custorder table . Most of the reporting queries running against it, are for the current year(2011) We want to partition this table against 5 separate physical hard disks using the partition key choosen. One suggestion is to use the 'order date' breaking the data up by calender year and the other is the 'customer id'. The reasoning behind partitioning the table using the 'order date' , so when querying for 2011 data just the latest partition would be searched all other partition would not be (partition elimnation). The other idea is to partition by 'customer id' (keeping in mind as I said before most reporting queries are for the current year) all the separate partition(on separate hard disks) would be invoked to search resulting faster performance. Also which partition key would provide better performance on new record inserts( primary key being customer-id and order-date) I am looking for advice or suggestions. thanks |
|
|
cns1202
Starting Member
7 Posts |
Posted - 2011-04-07 : 06:13:08
|
| Hi,We had a similar scenario..but i am not too sure about customer id.The order date would be a good choice for partitioning..in our case we used the FinancialYearMonth as the partitionKey.Also you probably might be aware but just as a thought create your indexes on partitions instead of whole table which will also help your speed and performance.Regards,Chirag Shah |
 |
|
|
|
|
|
choosing the right partition field