Splitting table into multiple tables

Question

Splitting table into multiple tables

March 1, 2022 at 6:33 am

This was removed by the editor as SPAM
March 1, 2022 at 6:37 am

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)

malczu Newbie Points: 1 More actions · Answer 4

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

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.