SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sub partitions


Sub partitions

Author
Message
Sunil.Aggusher
Sunil.Aggusher
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 16
Hello there,

Does SQL Server 2008 R2 support Sub partitions?

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86993 Visits: 45267
What do you mean by 'sub partitions'? What are you looking to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sunil.Aggusher
Sunil.Aggusher
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 16
I have a big table which is currently partitioned on a column called SupplierName. We get new data every week, for each supplier, and inserting data into this massive table takes a long time. Currently, we drop indexes, load data and then recreate indexes.

If I could further partition data under each Supplier then I could just switch partitions instead of loading data
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86993 Visits: 45267
Ah, no.

That said, sounds like you might want to re-do the partitioning on the table and instead partition on date rather than supplier. Then you have your fast load and fast delete (if necessary) based on time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sunil.Aggusher
Sunil.Aggusher
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 16
Thanks.

Will there be any performance degradation since most queries first go with Supplier and then date? In other words, do you see any chance of table level locks?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86993 Visits: 45267
Partitioning isn't about performance anyway. It's about maintainability, fast loads, etc. You may need to change nonclustered indexes, or you may not. Can't tell without a lot more information.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sunil.Aggusher
Sunil.Aggusher
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 16
Thanks for your responses. They were very helpful
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2111 Visits: 872
sf.onlineforums (9/19/2013)
Will there be any performance degradation since most queries first go with Supplier and then date? In other words, do you see any chance of table level locks?


That's indeed a valid concern. If your queries typically go by supplier disregarding the data, you will need to have indexes on supplier which is not partitioned-aligned. since else the data for the supplier is spread out all over the partitions, and performance will suffer.

Then again, if you are not loading or dropping data by supplier, I am not sure that there is much point of partitioning by supplier at all.

if you have a limited number of supplieries, one option would be to construct a an artifical key which is a combination of supplier and date, and now you can have the sub-partitions you desired. But you will need to modify you queries to use a range on this artificial key to query for a certain supplier.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Sunil.Aggusher
Sunil.Aggusher
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 16
Thanks Erland.

Loading data by date, every week, is the main reason for redesigning partitions to be based on dates. I like your idea of generating an artificial key. Do you recommend partitioning the table on artificial key or just creating indexes would do the same thing?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86993 Visits: 45267
Welcome to 'it depends' :-)

If you want to do fast loads and fast deletes (switch partitions in/out), then partition. If you want to do index maintenance at the partition level, then partition. If not, then probably no need to partition

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search