Optimize speed in removing duplicate records from a datatable.

Uniqueinc
Uniqueinc used Ask the Experts™
on
I am trying to remove duplicate rows from a datatable. The datatable was created by parsing out a MemoryStream and there is no way to change that part of the code. I can do the following code and remove any duplicate records. However it is slow and the more rows there are the worse it gets. There must be a better way but I'm not sure how. Is there a way to query the datatable directly in Visual Basic to return rows that match so i dont have to loop through every single record?
Private Sub FilterFaster(ByRef tbl As DataTable)
Dim copyTable As DataTable = tbl.Copy
    For Each r As DataRow In copyTable.Rows
        Dim foundOne As Boolean = False
        For i As Integer = (tbl.Rows.Count - 1) To 0 Step -1
            If CompareRows(r, tbl.Rows(i)) Then
                If foundOne Then
                    ' delete the row from the primary table
                    tbl.Rows(i).Delete()
                Else
                    ' this is the first instance of duplication, it is ok
                    foundOne = True
                End If
            End If
        Next
    Next
copyTable.Dispose()
end sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


good afternoon!

why not use the DISTINCT keyword on ur sql for
you to load only the distinct data on your datatable..


game-master

Author

Commented:
the datatable was not loaded from a sql query it was created by parsing out text passed in through a MemoryStream

Author

Commented:
Or did you mean you can use that DISTINCT keyword to get a copy of the tbl without duplicate rows?


YES, DAT IS WHAT I MEAN...

Author

Commented:
Ah thanks not too familiar with that keyword so i looked it up and works great. Super fast. Thanks
http://devpinoy.org/blogs/keithrull/archive/2008/02/07/how-to-get-the-distinct-rows-in-a-datatable-or-dataview.aspx 

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial