April 14, 2010 at 3:18 pm
I am researching different ways to scale-out our production database servers rather than scaling up. In our scenario, a very logical partitioning scheme would be to partition our data based on the location of a user. Our concept of location is a hierarchical structure such as building/floor/section/etc (representing a ragged hierarchy). It would be very beneficial to be able to use a partitioning function such that all descendants_of(some location) are stored on server1 while all descendants_of(some other location) are stored on server2. I can very easily draw boundaries that would define these partitions.
The problem is that it seems that the hierarchyid datatype is not valid as a partitioning column and I don't see any really obvious alternatives in our model. I can convert the hierarchyid to a varbinary which would then be ok, but I lose the ability to use the methods of the hierarchyid type to find children or parents without converting to a hierarchyid at runtime which causes table scans rather than seeks.
Does anyone have any alternatives that would be viable for our model?
Thanks
Tim Januario
April 14, 2010 at 3:24 pm
Switch to a Nested Sets hierarchy, and it will do all that and more.
Here's an article that will help explain some ways to implement this: http://www.sqlservercentral.com/articles/T-SQL/65540/
If you have questions after you take a look at that, and at the article by Joe Celko that I cited at the beginning of it, we can help with that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2010 at 3:42 pm
I'm not sure if that's quite what I'm looking for, though. I'm looking for a way to partition the data based on where a row sits in a hierarchy. If I were able to use the hierarchyid as a partitioning column, I would be able to put all users that are in building1 on one server and all users that are in building2 on a different server. Several tables have a location hierarchyid field and all of the data from those tables need to be sent to the correct partition. One option that I think would work is to add a field to the table that stores the id of the parent level that is a boundary for partitioning. For example, user John is located in building1/4th floor/section b and objectA is located in building1/2nd floor/section d. Both of these records should be on server1 based on the fact that they are both children of building1. If I were to add a "building" column, I could partition on that. However, it seems slightly inefficient to not only have a hierarchyid column that actually tells me that something is in building1 as well as a separate field just to store the reference to building1.
A correction to my first post: I am able to use the hierarchyid to partition the data. The problem is that the partitioned view that I use to map the data on the different servers and tables does not allow updates.
Tim Januario
April 15, 2010 at 6:30 am
Are you using table-level partitioning, or a federated (partitioned) view?
For a view, you'll need to have the table physically split, and have a column in the table that uniquely identifies that table, with a check constraint that forces the value. That's how the view will know which table to update.
For SQL 2008 table partitioning (Enterprise edition only), you can partition the table into multiple files and use a partitioning scheme to dynamically control what rows go in which files, all in one table. No view required. I've done this on calculated columns, which would probably do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply