如何在SQL Server中有效地合并两个层次结构？

我有两个带有hierarchyid字段的表,其中一个是具有需要合并到另一个的新数据的临时表(即,需要添加到主树的一组节点,其中一些可能已经是那里).

除了定义树结构(父/子关系)的hierarchyid列之外.每个表都有一个单独的列,其中包含唯一标识每个节点的节点标识符.也就是说,判断登台表中的节点是否已经在主表中的方法是通过节点ID,而不是通过hierarchyid列.

当然,需要执行的处理看起来像这样：

For each row,RS,in the staging table:
    If there is not already a row with the same Id as RS in the main table:
         Find the parent,PS,of the staging row
         Find the row,PM,in the main table that has the same node ID as PS
         Create a new child,RM of row PM
         Set PM's ID equal to the ID of RS

重要的是,这种方法只有在登台表中的树以广度优先顺序排序/遍历时才有效 – 这样当遇到RS时,可以保证其父PS在主表中已经有相应的行.

到目前为止,我在sql服务器中看到实现此目的的唯一方法是在登台表(已经排序)上使用游标,并为每一行调用一个存储过程,基本上完全按照上面描述的那样完成,并使用SELECT完成MAX()用于查找已作为PM的子项存在的最高hierarchyid,以便可以唯一地添加子项.

然而,这是一种非常低效的方法,并且对于我的目的来说太慢了.有没有更好的方法？

对于背景,这是我正在做的可行性检查.我需要弄清楚我是否可以在sql Server中快速执行此操作.如果事实证明我不能在数据库之外以另一种方式做到这一点.树的合并是(实际上,在某种意义上)是问题域所固有的,因此以不同方式构造数据或采取更广泛的视图并试图以某种方式完全避免执行此操作不是一种选择.

更新

根据要求,这是一个具体的例子.

表“staging”和“main”都有相同的两列：

hierarchy_id of type hierarchyid
   node_id of type bigint

初始内容

主要：

hierarchy_id    node_id
 /1/             1
 /1/1/           2
 /1/2/           3
 /1/3/           4

分期：

hierarchy_id    node_id
 /1/             1
 /1/1/           3
 /1/2/           5
 /1/1/1/         6

期望的内容

主要：

hierarchy_id    node_id
 /1/             1
 /1/1/           2
 /1/2/           3
 /1/3/           4
 /1/4/           5
 /1/2/1/         6

请注意,使用hierarchy_id / 1/1 /的临时表中的节点对应于目标表中具有hiearchy_id / 1/2 /的节点(这就是node_id很重要的原因 – 不能只复制hierarchy_id值).另请注意,具有node_id 6的新节点被添加为正确父节点的子节点,具有node_id 3的子节点,这就是hierarchy_id很重要的原因 – 它定义了任何新节点的树结构(父/子关系).任何解决方案都需要考虑到这两个方面.

解决方法

以这种方式对层次结构建模将导致问题.如果您没有序列化/瓶颈访问,则hierarchy_id列违反第一范式,并且合并过程将容易更新异常.

你应该考虑一个只有node_id和parent_id的表,看看它如何使你的合并问题变得无足轻重

node_id   parent_id
1         NULL
2         1
3         2
4         3

node_id   parent_id
1         NULL
3         1
5         2
6         1

您将使用递归查询,您可能会惊讶于执行计划的效率.如果必须具有展平的层次结构列,则可以使用递归查询创建索引视图.

如何在SQL Server中有效地合并两个层次结构？

解决方法

猜你在找的MsSQL相关文章