My favorite answer is as what the first sentence in this thread suggested.
(我最喜欢的答案是该线程的第一句话建议的内容。)
Use an Adjacency List to maintain the hierarchy and use Nested Sets to query the hierarchy. (使用邻接列表维护层次结构，并使用嵌套集查询层次结构。)
The problem up until now has been that the coversion method from an Adjacecy List to Nested Sets has been frightfully slow because most people use the extreme RBAR method known as a "Push Stack" to do the conversion and has been considered to be way to expensive to reach the Nirvana of the simplicity of maintenance by the Adjacency List and the awesome performance of Nested Sets.
(迄今为止的问题是，从邻接表到嵌套集的掩盖方法非常慢，因为大多数人使用称为“推栈”的极端RBAR方法进行转换，并被认为是昂贵的方法通过邻接表和嵌套集的出色性能达到维护简单性的必杀技。)
As a result, most people end up having to settle for one or the other especially if there are more than, say, a lousy 100,000 nodes or so. (结果，大多数人最终不得不适应一个或另一个，特别是如果存在超过100,000个左右的糟糕节点。)
Using the push stack method can take a whole day to do the conversion on what MLM'ers would consider to be a small million node hierarchy. (使用推栈方法可能需要一整天的时间来完成MLM'ers认为只有一百万个节点层次结构的转换。)
I thought I'd give Celko a bit of competition by coming up with a method to convert an Adjacency List to Nested sets at speeds that just seem impossible.
(我以为我想出一种方法，以一种似乎不可能的速度将“邻接表”转换为“嵌套”集，从而给Celko带来一些竞争。)
Here's the performance of the push stack method on my i5 laptop. (这是我的i5笔记本电脑上推入堆栈方法的性能。)
Duration for 1,000 Nodes = 00:00:00:870
Duration for 10,000 Nodes = 00:01:01:783 (70 times slower instead of just 10)
Duration for 100,000 Nodes = 00:49:59:730 (3,446 times slower instead of just 100)
Duration for 1,000,000 Nodes = 'Didn't even try this'
And here's the duration for the new method (with the push stack method in parenthesis).
(这是新方法的持续时间（括号内为推栈方法）。)
Duration for 1,000 Nodes = 00:00:00:053 (compared to 00:00:00:870)
Duration for 10,000 Nodes = 00:00:00:323 (compared to 00:01:01:783)
Duration for 100,000 Nodes = 00:00:03:867 (compared to 00:49:59:730)
Duration for 1,000,000 Nodes = 00:00:54:283 (compared to something like 2 days!!!)
Yes, that's correct.
(对，那是正确的。)
1 million nodes converted in less than a minute and 100,000 nodes in under 4 seconds. (在不到一分钟的时间内转换了100万个节点，在不到4秒的时间内转换了100,000个节点。)
You can read about the new method and get a copy of the code at the following URL.
(您可以阅读有关新方法的信息，并在以下URL上获得代码的副本。)
http://www.sqlservercentral.com/articles/Hierarchy/94040/ (http://www.sqlservercentral.com/articles/Hierarchy/94040/)
I also developed a "pre-aggregated" hierarchy using similar methods.
(我还使用类似的方法开发了“预汇总”层次结构。)
MLM'ers and people making bills of materials will be particularly interested in this article. (传销员和制作物料清单的人员将对本文特别感兴趣。)
http://www.sqlservercentral.com/articles/T-SQL/94570/ (http://www.sqlservercentral.com/articles/T-SQL/94570/)
If you do stop by to take a look at either article, jump into the "Join the discussion" link and let me know what you think.
(如果您确实停下来看任何一篇文章，请跳至“加入讨论”链接，让我知道您的想法。)
