Here's my scenario:
这是我的情景:
I've got a table of (let's call them) nodes. Primary key on each one is simply "node_id".
我有一个(让我们称之为)节点的表。每个主键都只是“node_id”。
I've got a table maintaining a hierarchy of nodes, with only two columns: parent_node_id and child_node_id.
我有一个维护节点层次结构的表,只有两列:parent_node_id和child_node_id。
The hierarchy is maintained in a separate table because nodes can have an N:N relationship. That is to say, one node can have multiple children, and multiple parents.
层次结构在单独的表中维护,因为节点可以具有N:N关系。也就是说,一个节点可以有多个子节点和多个父节点。
If I start with a node and want to get all of its ancestors (i.e. everything higher up the hierarchy), I could either do several selects, or do it all in one stored procedure.
如果我从一个节点开始并想要获得它的所有祖先(即层次结构中更高的一切),我可以做多次选择,或者在一个存储过程中完成所有操作。
Anyone with any practical experience with this question know which one is likely to have the best performance? I've read things online that recommend both ways.
任何对这个问题有实际经验的人都知道哪一个可能有最好的表现?我在线阅读了两种推荐方式。
3 个解决方案
#1
5
"which one is likely to have the best performance? " : No one can know ! The only thing you can do is try both and MEASURE. That's sadly enough the main answer to all performance related questions... except in cases where you clearly have a O(n) difference between algorithms.
“哪一个可能有最好的表现?”:没有人能够知道!你唯一能做的就是尝试和MEASURE。令人遗憾的是,所有与性能相关的问题的主要答案......除非您在算法之间明显存在O(n)差异。
And, by the way, "multiple parents" does not make a hierarchy (otherwise I would recommend to read some books by Joe Celko) but a DAG (Direct Acyclic Graph) a much harder beast to tame...
顺便说一句,“多个父母”并不构成一个等级(否则我会建议阅读Joe Celko的一些书)但是DAG(直接无环图)是一个更难以驯服的野兽......
#2
1
If performance is your concern, then that schema design is not going to work as well for you as others could.
如果您关注性能,那么该架构设计对您来说不会像其他人那样有效。
See More Trees & Hierarchies in SQL for more info.
有关详细信息,请参阅SQL中的更多树和层次结构。
#3
0
I think a general statements could lead into problem, because it depends on how you your queries respectively the stored procedure make of usage of the indices. To make a helpful declaration it would be necessary to compare the SQL of your selects and the stored procedure.
我认为一般性陈述可能导致问题,因为它取决于您的查询分别是存储过程如何使用索引。要做一个有用的声明,有必要比较您的选择的SQL和存储过程。
#1
5
"which one is likely to have the best performance? " : No one can know ! The only thing you can do is try both and MEASURE. That's sadly enough the main answer to all performance related questions... except in cases where you clearly have a O(n) difference between algorithms.
“哪一个可能有最好的表现?”:没有人能够知道!你唯一能做的就是尝试和MEASURE。令人遗憾的是,所有与性能相关的问题的主要答案......除非您在算法之间明显存在O(n)差异。
And, by the way, "multiple parents" does not make a hierarchy (otherwise I would recommend to read some books by Joe Celko) but a DAG (Direct Acyclic Graph) a much harder beast to tame...
顺便说一句,“多个父母”并不构成一个等级(否则我会建议阅读Joe Celko的一些书)但是DAG(直接无环图)是一个更难以驯服的野兽......
#2
1
If performance is your concern, then that schema design is not going to work as well for you as others could.
如果您关注性能,那么该架构设计对您来说不会像其他人那样有效。
See More Trees & Hierarchies in SQL for more info.
有关详细信息,请参阅SQL中的更多树和层次结构。
#3
0
I think a general statements could lead into problem, because it depends on how you your queries respectively the stored procedure make of usage of the indices. To make a helpful declaration it would be necessary to compare the SQL of your selects and the stored procedure.
我认为一般性陈述可能导致问题,因为它取决于您的查询分别是存储过程如何使用索引。要做一个有用的声明,有必要比较您的选择的SQL和存储过程。