I have the following requirement:
- We store daily order details for 10 companies for the past year.
Date Company Order
01-Jan-2010 Comp A Order # 1
01-Jan-2010 Comp A Order # 2
01-Jan-2010 Comp B Order # 1
31-Dec-2010 Comp A Order # 1
31-Dec-2010 Comp A Order # 2
31-Dec-2010 Comp B Order # 1
The database is loaded with new or updated order details for one or more companies every hour. (NB: Each company could have potentially thousands of orders arriving in every hourly cycle)
Currently we deal with this through SQL Server 2005 partitions as follows:
- Create an archive table partitioned by the date column. This table will look like the table above.
- Every hour collect together all newly arrived data into a separate table, also add unmodified rows from the archive, switch-out the current date partition in the archive table, then finally switch-in the newly prepared table.
This works well. However, since we are adding "unmodified rows from the archive" every hour this process is not optimal. For instance say we receive an order from only 1 company in a particular hour, according to the current implementation we end up copying accross the orders from all 9 other companies in order to populate the hourly table.
Can someone recommend a better way of doing this?
We have been considering creating a partition by Company instead of Date but then how do we handle the hourly switching process?