合并Excel数据到DataTable耗时过长，求性能优化方案

我遇到了性能问题，想寻求帮助：尝试将LINQ查询结果直接填充到DataTable或DataGridView中时耗时过长，不确定是Dictionary遍历方式有误，还是存在其他未察觉的问题。

补充说明 #

原始数据源是来自Excel表格的6000行数据，将其导入DataGridView仅需瞬间；但遍历Dictionary生成约2300行的目标DataTable时，耗时近1分钟。我的需求是合并具有相同编码、日期、位置的行，同时对指定字段求和（并非仅去重）。

原始实现代码 #

If reader.AsDataSet.Tables(0).Rows.Count > 0 Then

    Dim DistItemslist As New List(Of Eidos)
    Dim dgv As New DataGridView()
    Dim dic As New Dictionary(Of String, Integer)()
    Dim cellValue As String = Nothing
    Dim totalLU1 As Double = 0
    Dim totalreserved As Double = 0
    Dim distDT As New DataTable

    Dim distDR As DataRow = Nothing

    dgv.BindingContext = New BindingContext()
    dgv.DataSource = reader.AsDataSet.Tables(0)

    For Each col As DataGridViewColumn In dgv.Columns
        distDT.Columns.Add(dgv(col.Index, 0).Value)
        'DataGridView1.Columns.Add(dgv(col.Index, 0).Value, dgv(col.Index, 0).Value)
    Next

    dgv.Columns.Add("key", "key")
    dgv.Rows.RemoveAt(0)

    For i As Integer = 0 To dgv.Rows.Count - 1
        If IsDBNull(dgv(3, i).Value) Then
            dgv(3, i).Value = "0"
        Else
            dgv(3, i).Value = FormatDateTime(dgv(3, i).Value, DateFormat.ShortDate)

            If Split(dgv(3, i).Value, "/")(1) <> "02" Then
                dgv(3, i).Value = "30/" & Split(dgv(3, i).Value, "/")(1) & "/" & Split(dgv(3, i).Value, "/")(2)
            Else
                dgv(3, i).Value = "28/" & Split(dgv(3, i).Value, "/")(1) & "/" & Split(dgv(3, i).Value, "/")(2)
            End If
        End If
            dgv(10, i).Value = dgv(0, i).Value & "#" & dgv(1, i).Value & "#" & dgv(3, i).Value

    Next

    For i As Integer = 0 To dgv.Rows.Count - 1
        If Not dgv.Rows(i).IsNewRow Then
            cellValue = dgv(10, i).Value.ToString()

            If Not dic.ContainsKey(cellValue) Then
                dic.Add(cellValue, 1)
            Else
                dic(cellValue) += 1
            End If
        End If
    Next


    For Each pair In dic

        Dim query = (From row As DataGridViewRow In dgv.Rows
                     Where row.Cells(0).Value = Split(pair.Key, "#")(0) AndAlso row.Cells(1).Value = Split(pair.Key, "#")(1) AndAlso row.Cells(3).Value = Split(pair.Key, "#")(2)
                     Select row)

        For Each item In query
            totalLU1 = totalLU1 + CDbl(item.Cells(4).Value)
            totalreserved = totalreserved + CDbl(item.Cells(9).Value)
            'MsgBox(item.Cells(0).Value & " " & item.Cells(1).Value & " " & item.Cells(2).Value & " " & item.Cells(3).Value & " " & item.Cells(4).Value & " " & item.Cells(5).Value & " " & item.Cells(6).Value & " " & item.Cells(7).Value & " " & item.Cells(8).Value & " " & item.Cells(9).Value & " " & item.Cells(10).Value)
            'MsgBox(totalLU1)
            If item Is query.Last Then
                'DistItemslist.Add(New Eidos(item.Cells(0).Value, item.Cells(1).Value, item.Cells(2).Value, item.Cells(3).Value, totalLU1, item.Cells(5).Value, item.Cells(6).Value, item.Cells(7).Value, item.Cells(8).Value, totalreserved))
                'DataGridView1.Rows.Add(item.Cells(1).Value, item.Cells(2).Value, item.Cells(0).Value, item.Cells(3).Value, totalLU1, item.Cells(5).Value, item.Cells(6).Value, item.Cells(7).Value, item.Cells(8).Value, totalreserved)
                distDT.Rows.Add(item.Cells(0).Value, item.Cells(1).Value, item.Cells(2).Value, item.Cells(3).Value, totalLU1, item.Cells(5).Value, item.Cells(6).Value, item.Cells(7).Value, item.Cells(8).Value, totalreserved)
                totalreserved = 0
                totalLU1 = 0
            End If
        Next
    Next

    'DataGridView1.DataSource = DistItemslist

End If

优化后的代码 #

Dim XLDic As New Dictionary(Of String, String)()
        Dim SumDT As New DataTable
        Dim value As String
        Dim key As String


        For i As Integer = 0 To XLDT.Columns.Count - 1
            XLDT.Columns(i).ColumnName = XLDT.Rows(0).Item(i).ToString
            SumDT.Columns.Add(XLDT.Rows(0).Item(i).ToString)
        Next

        XLDT.Rows().RemoveAt(0)

        For i As Integer = 0 To XLDT.Rows.Count - 1
            If IsDBNull(XLDT.Rows(i).Item(3)) Then
                XLDT.Rows(i).Item(3) = "-"
            Else
                XLDT.Rows(i).Item(3) = FormatDateTime(XLDT.Rows(i).Item(3), DateFormat.ShortDate)

                If Month(CDate(XLDT.Rows(i).Item(3))) <> 2 Then
                    XLDT.Rows(i).Item(3) = "30/" & Split(XLDT.Rows(i).Item(3), "/")(1) & "/" & Split(XLDT.Rows(i).Item(3), "/")(2)
                Else
                    XLDT.Rows(i).Item(3) = "28/" & Split(XLDT.Rows(i).Item(3), "/")(1) & "/" & Split(XLDT.Rows(i).Item(3), "/")(2)
                End If
            End If
        Next

        For i As Integer = 0 To XLDT.Rows.Count - 1
            key = XLDT.Rows(i).Item(0)
            value = XLDT.Rows(i).Item(4)
            If Not XLDic.ContainsKey(key) Then
                XLDic.Add(key, value)   
            Else
                XLDic(key) = XLDic(key) & "..."
            End If
        Next


        For Each pair In XLDic
            SumDT.Rows.Add(pair.Key, pair.Value)
        Next

        DataGridView1.DataSource = SumDT
    End If
End If

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