From the article...
The speed difference and IO difference can be significant on the two. For example, I have a hierarchy in one of my databases with 2,700 nodes in it, going up to six levels deep. If someone at the top of that hierarchy signs in, it takes 11 seconds for my server to resolve the whole hierarchy and determine what data that person has access to (this is a security access hierarchy that controls much of what is displayed to customers on a web site). That’s using the adjacency model. Using a nested sets table, that same hierarchy takes less than 1 millisecond. (This isn’t the one with multiple parents. Different company.)If this same database didn’t have a lot of updates to the hierarchies, I’d definitely use a straight-up nested sets hierarchy, and have much faster web pages. But it does have a lot of updates, sometimes several per minute, sometimes several at the same time. [font="Arial Black"]Each nested sets rebuild takes about 20-30 seconds to finish[/font], and locks the table pretty aggressively while it’s running.
Hi Gus,
I've had reason to revisit this article and the paragraphs above caught my eye, especially the two emphasized areas.
With that in mind, which method are you using to convert the Adjacency List to a Nested Set Model? Is it the familiar "push stack" method from Joe Celko's book/article postings? And was it for the ~2700 rows you mentioned?
Thanks, Gus.
--Jeff Moden
Change is inevitable... Change for the better is not.