Splitting table into multiple tables

  • SQLAddict01 wrote:

    We use enterprise edition.

    Yes I think partitioning would be helpful for stats to run fast, but I did not see any performance improvements in the queries.

    Normally, partitioning will actually make things run a bit slower due to some additional overhead.  The big reason to partition is for index maintenance, statistics maintenance, possibly reduced backups, the ability to do piece-meal restores, and the ability to rapidly SWITCH partitions of data into and out of the table.  Performance improvements for selects are rare and, even if you get lucky with "partition elimination", a properly groomed'n'tuned monolith structure with the correct indexes will be a partitioned table for performance.

    And, yes... I've proven that in one of my own nearly 2TB tables.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Partitioning often serendipitously helps with performance.  That's because people will create a different clustered index to help with the partitioning process and that new clustered index actually does help the performance.  People then mistakenly attribute the better performance to the partitioning when in fact it was changing the clustered index that really helped performance.  I've seen this happen many times.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I agree.  I hope people don't read that and think it was the partitioning that did it though.  It was the better index that did it.  I also state that I've done the experiment of removing the partitioning and just keeping the index (obviously rebuilt after dropping the partitioning) and it did better after reverting to a monolithic structure.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hey SQLAddict01

    What did you end up doing? I have similar issue. I have last 3 months worth of data that is being used most of the time. Remaining 2 years of data is used once in a while. Since everything is in the same db. Even if we add some indexes they become large and we can't cover all of the cases. So table scans are slow due to large amount of data. We were thinking about splitting this data somehow.

     

    Thanks

  • malczu wrote:

    Hey SQLAddict01

    What did you end up doing? I have similar issue. I have last 3 months worth of data that is being used most of the time. Remaining 2 years of data is used once in a while. Since everything is in the same db. Even if we add some indexes they become large and we can't cover all of the cases. So table scans are slow due to large amount of data. We were thinking about splitting this data somehow.

    Thanks

    If you're looking for advice, best to start a new thread. Your situation may be quite different, and SQLAddict's solution may not work for you.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 7 posts - 16 through 21 (of 21 total)

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