Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Partitioning Strategy Recommendations

I have am designing a database which will hold testing records. For one of the tests that I am storing, there are up to 500 results that need to be stored. For example, the log I would put into the database has values like, serialNumber, TestDate, TestTime, Operator, and Test_X. Test_X has a few parameters that need to be stored like, attrib_X, and Attrib_Y, and it has an array of 500 test results. The order of the results are not important.

Originally I created a table for the GeneralTestInfo, and another table for Test_X attributes (attrib_X, attrib_Y, etc.) and then another table for the 500 Test_X result values. But I realize that the Test_XResults table would grow to be very big way to fast.

Does anyone have any good suggestions on how to partition this data?

1 Solution
i do not know the sizes you are going to get but if you really think it will get big then think what are your main  selects for reporting from this data.
Then make good indexes to support that selects.
How big you think it will get the next month the next year? And the selects from it will they need data from previous month or previous year?
willmrkAuthor Commented:
I read the MS SQL Server 2005 paper on partiioning tables. I now understand that the key part of partioning is seperating the data in a way so that the query optimizer will be able to ignore certain tables. Originally, I was thinking of taking the 500 results, spliting equally to groups of 125 and storing in four different tables. This would provide near ZERO for performance increase, because on nearly every query, the 4 tables would have to be joined.

I expect to get around 10000 records a month, which equates to 5000000 test_x results a month. Not a huge volume, but want to avoid performance problems.

I'm thinking that a new patiion created every 3 or 6 months would do. Now to do the investigation on how to setup the partition function, schema, and views. I think I will be able to figure it out, but anybody willing to throw some advice or sample instructions my way would be greatly appreciated.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now