VB.NET优化Sqlite3写性能-秒级百万条写入

测试百万条数据写入

测试100,0000行数据，4列。耗时5468毫秒，.环境.net framework 4.7.2 release模式。代码如下

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim t1 As Date = Now
    Try
        ' 创建一个内存数据库
        Using dbMemory As New SQLiteConnection("Data Source=:memory:")
            dbMemory.Open()

            Dim cmdMemory As New SQLiteCommand("PRAGMA synchronous = OFF", dbMemory)
            cmdMemory.ExecuteNonQuery()

            ' 创建一个带有自增 ID 的表
            cmdMemory.CommandText = "CREATE TABLE Test(ID INTEGER PRIMARY KEY AUTOINCREMENT, var0 INTEGER, var1 REAL, var2 TEXT);"
            cmdMemory.ExecuteNonQuery()

            Dim sql As String = "INSERT INTO Test (var0, var1, var2) VALUES (?, ?, ?)"
            Using prepareCmd As New SQLiteCommand(sql, dbMemory)
                Dim maxCount As Integer = 1000000 ' 插入的记录总数
                Dim batchSize As Integer = 10000 ' 每批次的记录数量

                ' 开始事务
                cmdMemory.CommandText = "BEGIN TRANSACTION"
                cmdMemory.ExecuteNonQuery()

                For i As Integer = 0 To maxCount - 1
                    prepareCmd.Parameters.Clear()
                    prepareCmd.Parameters.AddWithValue("@var0", 1)
                    prepareCmd.Parameters.AddWithValue("@var1", 2.0)
                    prepareCmd.Parameters.AddWithValue("@var2", "hello sqlite3.")
                    prepareCmd.ExecuteNonQuery()

                    ' 每达到一定批次大小提交一次
                    If (i + 1) Mod batchSize = 0 Then
                        cmdMemory.CommandText = "COMMIT"
                        cmdMemory.ExecuteNonQuery()
                        cmdMemory.CommandText = "BEGIN TRANSACTION"
                        cmdMemory.ExecuteNonQuery()
                    End If
                Next

                ' 提交最后一个事务
                cmdMemory.CommandText = "COMMIT"
                cmdMemory.ExecuteNonQuery()
            End Using

            ' 现在将内存数据库导出到文件
            Dim filePath As String = "exported.db3"

            ' 确保目标数据库连接是开放的
            Using dbFile As New SQLiteConnection($"Data Source={filePath};")
                dbFile.Open()

                ' 使用内存数据库备份到文件
                Using backup As New SQLiteConnection($"Data Source=:memory:;")
                    backup.Open()
                    dbMemory.BackupDatabase(dbFile, "main", "main", -1, Nothing, Nothing)
                End Using
            End Using

            Console.WriteLine("写入完成，耗时：" & (Now - t1).TotalMilliseconds & " 毫秒")
        End Using
    Catch ex As Exception
        Console.WriteLine("Error: " & ex.Message)
    End Try
End Sub