时间戳

首页 > 解决方案 > 循环中的sqlite更新需要很长时间

vb.net - 循环中的sqlite更新需要很长时间

问题描述

我在一个循环中只更新 20 行，但我获得的最佳性能是 20 行大约 2.5 秒。我该怎么做才能使这个简单的更新更快？

这是我试过的：

Dim connectionString As New SQLite.SQLiteConnection("data source=" & Application.StartupPath & "\db.db3; Version=3;")
Dim connection = New SQLite.SQLiteConnection(connectionString)
connection.Open()
Dim com3 As New System.Data.SQLite.SQLiteCommand(connection)
For Each row As DataRow In table.Rows
     com3.CommandText = "UPDATE Results SET LastScore=" & row.Item("position") & " WHERE TeamID='" & row.Item("teamid") & "'"
     com3.ExecuteNonQuery()
Next
connection.Close()

和

Dim connectionString As New SQLite.SQLiteConnection("data source=" & Application.StartupPath & "\db.db3; Version=3;")
Using con As New SQLiteConnection(connectionString)
    con.Open()
    Using c As New SQLiteCommand(con)
        c.CommandText = "UPDATE Results SET LastScore= @Pos WHERE TeamID = @ID"
        For Each row As DataRow In table.Rows
            c.Parameters.AddWithValue("@Pos", row.Item("position"))
            c.Parameters.AddWithValue("@ID", row.Item("teamid"))
            c.ExecuteNonQuery()
        Next
    End Using
    con.Close()
End Using

标签： vb.netsqliteloops

解决方案

感谢 muffi 的回答，这个问题现在已经解决。添加 BeginTransaction()

Dim connectionString As New SQLite.SQLiteConnection("data source=" & Application.StartupPath & "\db.db3; Version=3;")
    Using con As New SQLiteConnection(connectionString)
        con.Open()
        Using t As SQLiteTransaction = con.BeginTransaction()
            Using c As New SQLiteCommand(con)
                c.CommandText = "UPDATE Results SET LastScore= @Pos WHERE TeamID = @ID"
                For Each row As DataRow In table.Rows
                    c.Parameters.AddWithValue("@Pos", row.Item("position"))
                    c.Parameters.AddWithValue("@ID", row.Item("teamid"))
                    c.ExecuteNonQuery()
                Next
            End Using
            t.Commit()
        End Using
        con.Close()
    End Using

推荐阅读