基于父ID递归查询自引用DataTable子节点并批量更新列的优化方案

我有一个自引用结构的表，结构如下：

已知一个初始Key值，需要递归查询其所有子节点并更新Description字段。表中约有27000条记录，层级较多。我尝试用以下代码更新，但耗时极长：

Private Sub ChangeDescriptionChildRows(ByRef row As DataRow, ByRef table As DataTable)
    UpdateDescription(row) 'Already having Root node

    For Each childRow As DataRow In table.Select("parent=" & row("key"))
        UpdateDescription(childRow, table) 'Recursion
    Next
End Sub

Private Sub UpdateDescription(ByRef row As System.Data.DataRow)
    Dim description As String = ""
    If row("key") = "A" Then
        description = "This is Root Node"
    ElseIf row("Key") = "B" Then
        description = "Something"
    End If
    row("description") = description
End Sub

想知道有没有更好的优化方式，比如用PLINQ/Parallel.ForEach来提速？

1. 构建父节点索引，避免全表扫描 #

DataTable.Select()每次调用都会触发全表扫描，这是性能瓶颈的核心。提前按parent字段构建字典映射，把查询复杂度从O(n²)降到O(n)：

' 提前构建父节点到子节点的映射字典
Dim parentToChildren As New Dictionary(Of String, List(Of DataRow))()
For Each row As DataRow In table.Rows
    Dim parentKey As String = If(row("parent") Is DBNull.Value, Nothing, row("parent").ToString())
    If Not parentToChildren.ContainsKey(parentKey) Then
        parentToChildren(parentKey) = New List(Of DataRow)()
    End If
    parentToChildren(parentKey).Add(row)
Next

后续遍历直接从字典中取对应子节点，无需重复扫描全表。

2. 用迭代替代递归，避免栈溢出与调用开销 #

递归在层级过深时容易触发栈溢出，且函数调用本身有额外开销。改成迭代式的广度/深度优先遍历：

Private Sub UpdateAllDescendants(rootKey As String, table As DataTable)
    ' 构建父节点映射索引
    Dim parentToChildren As New Dictionary(Of String, List(Of DataRow))()
    For Each row As DataRow In table.Rows
        Dim parentKey As String = If(row("parent") Is DBNull.Value, Nothing, row("parent").ToString())
        If Not parentToChildren.ContainsKey(parentKey) Then
            parentToChildren(parentKey) = New List(Of DataRow)()
        End If
        parentToChildren(parentKey).Add(row)
    Next

    ' 广度优先迭代遍历
    Dim queue As New Queue(Of DataRow)()
    Dim rootRow As DataRow = table.Select($"key='{rootKey}'").FirstOrDefault()
    If rootRow IsNot Nothing Then
        queue.Enqueue(rootRow)
        While queue.Count > 0
            Dim currentRow As DataRow = queue.Dequeue()
            UpdateDescription(currentRow)
            ' 加入当前节点的所有子节点
            Dim childKey As String = currentRow("key").ToString()
            If parentToChildren.ContainsKey(childKey) Then
                For Each childRow As DataRow In parentToChildren(childKey)
                    queue.Enqueue(childRow)
                Next
            End If
        End While
    End If
End Sub

3. 并行处理的正确姿势 #

如果要使用PLINQ/Parallel.ForEach，需注意DataRow的线程安全特性：只要保证同一时间只有一个线程操作单条DataRow，就可以安全并行。建议先收集所有需要更新的行，再批量并行处理：

' 先收集所有目标行（根节点+所有子节点）
Dim allTargetRows As New List(Of DataRow)()
' 用上述迭代方法将所有需要更新的行加入allTargetRows...

' 并行更新描述字段
allTargetRows.AsParallel().ForAll(Sub(row)
                                      UpdateDescription(row)
                                  End Sub)

如果UpdateDescription中涉及共享资源操作，必须加锁；单纯更新单条行的字段则无需额外同步。

4. 优化UpdateDescription逻辑 #

用字典映射替代多分支ElseIf判断，提升匹配效率：

' 提前构建key到描述的映射表
Dim keyToDesc As New Dictionary(Of String, String) From {
    {"A", "This is Root Node"},
    {"B", "Something"}
}

Private Sub UpdateDescription(row As DataRow)
    Dim key As String = row("key").ToString()
    row("description") = If(keyToDesc.ContainsKey(key), keyToDesc(key), "")
End Sub

这种方式在判断条件较多时，性能远优于分支判断。

内容的提问来源于stack exchange，提问作者Yashas