Partitioning Strategy Recommendations

Posted on 2007-10-18
Last Modified: 2008-01-17
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?

Question by:willmrk
    LVL 5

    Accepted 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?

    Author Comment

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now