Post reply

Table partitioning

  • September 30, 2016 at 7:40 am

    #313962

    Hi,

    I've a table with 1.000.000 rows where active records and inactive records are kept, since the user can search on both status (only 5% or less of the time they search inactive records). The inactive records are about 30% of the entire table.

    Is it worth creating a partition on the isActive column to "split" the data or adding a filter on the main indexes should be enough?

    Thanks,

    Pedro


    If you need to work better, try working less...

  • September 30, 2016 at 8:28 am

    #1904174

    I don't think you'll gain anything from partitioning. I'd add the column to indexes. Partitioning is really for maintenance not performance, although you do get some performance from partition elimination.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • September 30, 2016 at 8:29 am

    #1904175

    PiMané (9/30/2016)

    Hi,

    I've a table with 1.000.000 rows where active records and inactive records are kept, since the user can search on both status (only 5% or less of the time they search inactive records). The inactive records are about 30% of the entire table.

    Is it worth creating a partition on the isActive column to "split" the data or adding a filter on the main indexes should be enough?

    Thanks,

    Pedro

    If you are looking for a performance boost, partitioning probably won't help. I'd start off with one or more filtered indexes.

  • September 30, 2016 at 8:46 am

    #1904181

    Thanks, that what I thought also...

    And right now the column has 90% active and 10% inactive so for 99% of the cases is almost a table scan..

    Thanks,

    Pedro


    If you need to work better, try working less...

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply