Fastest method of filling a List(Of DataRow): SQLiteDataAdapter or SQLiteDataReader?-CodePudding

I had always seen comments that a SQLiteDataReader is faster than a SQLiteDataAdapter, so I tried the following comparison on a table of 355,020 rows.

Test 1:

    Dim DRows As New List(Of DataRow)
    Try
        Connect() ' This creates and opens the connection
        Using tr As SQLiteTransaction = conn.BeginTransaction
            Using cmd As New SQLiteCommand
                cmd.Connection = conn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT * FROM `admin_class`;"
                Using da As New SQLiteDataAdapter(cmd)
                    Dim dt As New DataTable
                    da.Fill(dt)
                    If dt.Rows.Count > 0 Then
                        For a As Integer = 0 To dt.Rows.Count - 1
                            DRows.Add(dt.Rows(a))
                        Next
                    End If
                End Using
            End Using
            tr.Commit()
        End Using
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        conn.Close()
    End Try

Test 2:

    Dim DRows As New List(Of DataRow)
    Try
        Connect() ' This creates and opens the connection
        Using tr As SQLiteTransaction = conn.BeginTransaction
            Using cmd As New SQLiteCommand
                cmd.Connection = conn
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT * FROM `admin_class`;"
                Dim dr As SQLiteDataReader = cmd.ExecuteReader()
                Using dt As New DataTable
                    dt.Load(dr)
                    If dt.Rows.Count > 0 Then
                        For a As Integer = 0 To dt.Rows.Count - 1
                            DRows.Add(dt.Rows(a))
                        Next
                    End If
                End Using
                dr.Close()
            End Using
            tr.Commit()
        End Using
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        conn.Close()
    End Try

Surprisingly, the first code took 2.361 seconds, and the second code took 4.970 seconds. What am I doing wrong?

CodePudding user response：

I can't comment on SQLite directly but I just executed this code:

Imports System.Data
Imports System.Data.SqlClient

Module Program

    Sub Main(args As String())
        Dim timer = Stopwatch.StartNew()

        Dim table As New DataTable

        Using connection As New SqlConnection("connection string here"),
              command As New SqlCommand("SELECT * FROM MyTable", connection)
            connection.Open()

            Using reader = command.ExecuteReader()
                table.Load(reader)
            End Using
        End Using

        timer.Stop()

        Console.WriteLine(timer.Elapsed)
        Console.ReadLine()
    End Sub

End Module

in a .NET 6 Console app three times against a table with just over 1.5 million records and got the following output:

00:00:07.1881430

00:00:07.2306110

00:00:07.1166125

I then switched to this code:

Imports System.Data
Imports System.Data.SqlClient

Module Program

    Sub Main(args As String())
        Dim timer = Stopwatch.StartNew()

        Dim table As New DataTable

        Using connection As New SqlConnection("connection string here"),
              adapter As New SqlDataAdapter("SELECT * FROM MyTable", connection)
            adapter.Fill(table)
        End Using

        timer.Stop()

        Console.WriteLine(timer.Elapsed)
        Console.ReadLine()
    End Sub

End Module

and got this output:

00:00:03.5211019

00:00:03.4162924

00:00:03.4639760

That tells me that you're not doing anything wrong and the data adapter is just faster than the data reader when populating a DataTable. I'm guessing that it's because there's some doubling up of work with the data reader, where the data adapter knows the data is going directly to the DataTable, but I don't know for sure. I will do a bit of investigation and see if I can pinpoint the reason and post back if I find something.