looking for more efficient way to concatinate values in a datatable

[camatulli]

i'm having an major performance issue with a concatination query i wrote against a datatable and was looking for a way to either speed up the query or a whole nother way of doing it which performs better...

at present my app spends 2 min processing this one section, while i can do the two loops individually in about 2 seconds.

Here is the code:

        For Each dr As DataRow In Main.DS_Old.Tables(LeftTable).Select(filter, "location,type desc")
            Dim CATValues As String = Nothing
            If dr.RowState <> DataRowState.Deleted And dr.RowState <> DataRowState.Detached Then
                Select Case dr("name").ToString.ToLower
                    Case "value"
                        For Each DRsameLocation As DataRow In Main.DS_Old.Tables(LeftTable).Select(filter & " AND PK<>" & dr("PK") & " AND Location='" & dr("Location") & "' AND type='" & dr("type") & "' AND Name='" & dr("Name") & "'")
                            CATValues += Chr(215) & DRsameLocation("loc") & Chr(214) & DRsameLocation("value")
                            DRsameLocation.Delete()
                        Next
                        If CATValues = Nothing Then
                            dr("value") = dr("loc") & Chr(214) & dr("value")
                        Else
                            dr("value") = dr("loc") & Chr(214) & dr("value") & CATValues
                            dr("loc") = "MV"
                        End If
                End Select
            End If
        Next

 

[cjard]

Specifying what it is supposed to do might help us out a bit..

 

[camatulli]

Specifying what it is supposed to do might help us out a bit..

it concatinates the "value" column on all records that have the same value in the column location, name and type.