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

[IT_Researcher]

We are using VB.NET with System.Data.DataTable objects that contain large volumes of data. We are evaluating which approach is the fastest and most efficient for selecting rows in specific scenarios.
1. Single row
2. Multiple rows
3. Single row with simple condition
4. Single row with complex condition
5. Multiple rows with simple condition
6. Multiple rows with complex condition
Which method is best for each case? Rows.Find(), DataTable.Select(), Manual Loops (For Each), LINQ, DataView.RowFilter? Any other methods?
Any performance benchmarks or timing comparisons for these methods would be helpful.

[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

[RedHeadedLefty]

From a data perspective, can keep large tables on the DB and run the SQL necessary to pull whatever you need, as opposed to bringing those tables into your app.

[Shaggy Hiker]

I agree. Whenever possible, get just what you need. It may seem like working with a datatable in memory is going to be superior to doing round trips to the DB, and sometimes that is true, but often it is not. Is there a reason why you need large amounts of data in memory? If so, then you have to do so, but consider whether there isn't an alternative.

[wes4dbt]

Would also be helpful to know what "large volumes of data" is in this case. 10k, 100k, ....... rows. Are the columns per row large?

[gigemboy]

IT_Researcher looking for free help. Best performance? Let the DB engine handle it.