April 2, 2008 at 1:13 pm
Jeff Moden (4/1/2008)...
Heh... I know I'm not supposed to cheat, but I do... I maintain the adjacency model... when there's a change move (not just an insert), the whole bloody world changes when the position being moved changes... as you said, those types of changes are very easy to make in an adjacency model. Right after those changes are done, BOOM, I run the code to rebuild the nested set model. When it's done building, I simply "flip" tables (views in 2k and synonyms in 2k5) so the web services and the users see about a 10ms delay if they see it at all. Properly written, the changes don't take that long to run.
However... if you already have a working system, the ROI you spoke of becomes incredibly important on what the long term tradeoffs caused by growth actually are. I like to anticipate growth but if you've already done that and are a bazillion percent sure than virtually no growth will occur, then why spend the moneY? Ya just gotta be sure, that's all.
By the way, there is another alternative to both the adjacency and nested set models. How many total "nodes" do you have and what datatype do you use as the PK ("ChildID" in this case? I need to make sure we can even do it before I spend any time suggesting it.
The hybrid solution sounds workable, but I think it's more gun than this hunt requires. I'm also concerned about adding complexity to a system, since that increases entropy. I'll see about some tests on that.
On the long-term growth, yeah, there will be some. Hopefully a lot. (This is a big-money product for this company.) With the tests I've done, I'm not worried about that in the slightest. Hoping for it, actually. I've taken my tests up to 5M nodes on a test machine. It's not really possible for this market to require more than that, even if we achieve a total monopoly (which is unlikely but marginally possible).
On the current data: PK is int identity(1,1), current total nodes is just over 29k. I expect to more than double that in Q4 this year, and probably double it again in Q1 next year. Growth in Q2 and Q3 should be close to zero (this product is seasonal).
- 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 2, 2008 at 1:24 pm
TheSQLGuru (4/2/2008)
G2, your issue seems to be different in nature from my client's. They took the hierarchy as an initial set of data and then joined it to between 3 and 7 other tables to extract out reporting data. It was the nested loop joins picked by the optimizer that was the killer due to the sometimes very large numbers of rows that came out of the joins.I am actually impressed that you were able to develop a system as functional as it is for such a diverse and complex dataset.
Yeah, for something like that, I'd denormalize the hierarchy too. Makes total sense that way. Jeff's solution with hybridizing adjacency and nested sets is very similar in function. I can see either one of those working if the data were just a little less volatile. And volatility increases more rapidly than volume in this case.
Thanks for the compliment, but I can't really take full credit for this. It's pretty standard code and tables. I've just refined the process a bit for higher performance.
For example, in adjacency hierarchies, you usually have a single ID field, and a single ParentID field. These are joined through a standard FK constraint. I added a HierarchyID field to this. It's part of both the PK and the FK. The index on that speeds up the queries like you wouldn't believe! Each top-level node has a unique HierarchyID, and every level below it has the same number.
- 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 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply