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?

  • Can you better explain the goal of this process? – mrdenny Jan 27 '11 at 17:44
  • Hi, mrdenny, the goal is to keep the archive table as free of write operations as possible. The archive table is heavily indexed and is the source for client application queries. If the hourly receive data is written directly into the archive table then we would have problems of index fragmentation, statistics getting outdated and locks being taken - all of which would affect query performance. Let me know if you think differently. Thanks – Noel Abrahams Jan 28 '11 at 11:35

Ideally you would want to only switch in the newly added hour, w/o copying nor touching the previous data. But that would require 1 partition per hour and you would run out of partitions in 4 days. Even with SQL 2008 SP2 increased partitions support you could only store less than 2 years of data. As a stretch, with 3 tables and switching partitions from current to older and then to even older one you'd be able to cover 5 years, which is a typical data retention policy requirement, but I would not recommend this lightheartedly.

For the situation you're in, I actually think you are doing the best possible solution. What I would consider would be to prepare the next hour partition ahead of time:

  • at H hour you start receiving the latest data and store it in table T. This table was prepared ahead of time with the existing archived data for the current partition (in other words, is a copy of the current partition).
  • When the data receiving is complete, create a copy T' of table T, using a fast bulk operation (select into, bcp, insert bulk, openrowset bulk etc). This T' copy becomes the table to receive new data for the next hour.
  • switch in the table T
  • rename T' to T, you're ready for the next data to arrive.

Of course, I made an assumption here that once imported, the data is not modified, which is fairly common in most ETL scenarios.

  • Hi, Remus, I had read up on the partition limitation. Storing data every hour is not exactly the requirement: we receive data by the hour but for a given day - not for a specific hour. So we end up storing data for a company only one instance per day (inspite of this being updated every hour at a given point every day, say 1800 hrs, we would say that is the official data for company A for date X). Thank you for your response. It is helpful to know that I am not missing any tricks. Please also take a look at my comment to mrdenny as that sheds some light on the motivation. – Noel Abrahams Jan 28 '11 at 11:42
  • Then you shouldn't copy any data. You add data to the new table as it comes, from each company. At midnight you switch in the new – Remus Rusanu Jan 28 '11 at 20:37
  • Yes, I could do that. But we also have a requirement to make the data available for reporting as soon as it arrives. So we do need to switch the data in every hour. I guess there is nothing I can do to improve the situation. – Noel Abrahams Jan 31 '11 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.