Fastest & Most Efficient DataTable Row Selection Methods in VB.NET (Single/Multiple,

[dday9]

What version of .NET are you using?

Regardless, ADO.NET operations will probably be the fastest because it is the de facto low-level foundation for data access in .NET. The reason I ask is because Microsoft revamped ADO.NET around .NET 9 when they deprecated System.Data.SqlClient in favor of Microsoft.Data.SqlClient. This change really boosted performance, particularly with bulk copying, but with your typical every day selects too.

I also need to know how you are selecting the data for a single row. Because if DataTable has its PrimaryKey set, then the absolute 100% best way to select a row is by using the Find method.

Also, here's an older article but still relevant: https://chrisbitting.com/2016/05/04/...rallel-vs-for/

Basically, the idea is that if you cannot use the Find method, then the second-best approach for large datasets is to use a parallel for loop to iterate over the result set. If you're returning a single row, then have it exit the loop prematurely and if you're returning multiple rows then don't. Here are some simple examples:

Public Function FindSingleParallel(table As DataTable, predicate As Func(Of DataRow, Boolean)) As DataRow
    Dim result As DataRow = Nothing

    Try
        Parallel.ForEach(table.AsEnumerable(), Sub(row, loopState)
            If (result IsNot Nothing) Then
                Return
            End If
            If (predicate(row)) Then
                If (Interlocked.CompareExchange(result, row, Nothing) Is Nothing) Then
                    loopState.Stop()
                End If
            End If
        End Sub)
    Catch ex As OperationCanceledException
        ' Expected when a match is found
    End Try

    Return result
End Function

Public Function FindManyParallel(table As DataTable, predicate As Func(Of DataRow, Boolean)) As IEnumerable(Of DataRow)
    Dim results As New ConcurrentBag(Of DataRow)

    Parallel.ForEach(table.AsEnumerable(), Sub(row)
        If (predicate(row)) Then
            results.Add(row)
        End If
    End Sub)

    Return results
End Function