在SQLite中如何更快地执行UPDATE操作 + 开始事务

Question

这与spatilite有关（不仅限于SQLite）。

我有一个文件数据库（xyz.db），我正在使用SQLiteconnection（SQLiteconnection扩展到spatialite）。

我有许多需要更新到数据库的记录。

                for (int y = 0; y < castarraylist.Count; y++)
                {
                    string s = Convert.ToString(castarraylist[y]);

                    string[] h = s.Split(':');

                    SQLiteCommand sqlqctSQL4 = new SQLiteCommand("UPDATE temp2 SET GEOM = " + h[0] + "WHERE " + dtsqlquery2.Columns[0] + "=" + h[1] + "", con);
                    sqlqctSQL4.ExecuteNonQuery();

                    x = x + 1;
                }

在上述逻辑中，castarraylist 是一个包含需要处理到数据库中的值的 Arraylist。

当我检查以上代码时，每分钟可以更新约 400 条记录。

有没有办法能够提高性能？

注意：（文件数据库不支持多线程）

2. 开始事务

假设我想要在 Spatialite 中运行两个（或数百万个）更新语句，并使用单个事务，这是否可能？

我在网上阅读并为自己准备了下面的语句（但没有成功）。

BEGIN TRANSACTION;
UPDATE builtuparea_luxbel SET ADMIN_LEVEL = 6 where PK_UID = 2;
UPDATE builtuparea_luxbel SET ADMIN_LEVEL = 6 where PK_UID = 3;
COMMIT TRANSACTION; 

以上语句未更新我的数据库记录。SQLite不支持BEGIN TRANSACTION吗？我是否遗漏了什么？

如果我需要运行单个语句，则更新所需的时间太长，如上所述...

- Hardik

Answer 1

SQLite支持事务，您可以尝试以下代码。

using (var cmd = new SQLiteCommand(conn))
using (var transaction = conn.BeginTransaction())
{
    for (int y = 0; y < castarraylist.Count; y++)
    {
        //Add your query here.
        cmd.CommandText = "INSERT INTO TABLE (Field1,Field2) VALUES ('A', 'B');";
        cmd.ExecuteNonQuery();
    }
    transaction.Commit();
}

- Suresh

---

这个逻辑与上面有什么不同？因为我们在这里也正在运行 for 循环，它循环了1000次。 - Hardik

你可以看到上面的查询将会对castarraylist中的所有记录执行，事务完成后将会提交，你可以尝试以上代码并查看结果。 - Suresh

是的，它有所改善，但仍然不够有效率。对于100000条记录，需要花费超过50分钟的时间。因此，我手动收集了执行每批5000条记录所需的时间。我注意到在处理每批数据时，时间会逐渐增加。5000条记录的处理时间第一批 > 1:11 分钟 第二批 > 1:25 分钟 第三批 > 1:32 分钟 第四批 > 1:40 分钟 第五批 > 1:47 分钟 第六批 > 1:52 分钟 ... ... ... 第17批 > 3:32 分钟 第18批 > 3:44 分钟 第19批 > 4:02 分钟 第20批 > 4:56 分钟为什么会这样呢？需要运行其他内存增量命令吗？ - Hardik

你不应该重复使用SQLiteCommand对象来执行如此重要数量的查询：这会导致内存开销。 - Larry

Answer 2

• 数据库事务的主要目标是要么全部完成，要么一件也不做，如果出现故障，则回滚；

• 通过更改其CommandText属性并反复执行，重用相同的 SQLiteCommand对象可能会更快，但会导致内存开销：如果您有大量查询需要执行，则最好在使用后处理该对象并创建一个新对象；

ADO.NET事务的常见模式：

using (var tra = cn.BeginTransaction())
{
    try
    { 
        foreach(var myQuery in myQueries)
        { 
            using (var cd = new SQLiteCommand(myQuery, cn, tra))
            {
                cd.ExecuteNonQuery();
            }
        }

        tra.Commit();
    }
    catch(Exception ex)
    {
        tra.Rollback();
        Console.Error.Writeline("I did nothing, because something wrong happened: {0}", ex);
        throw;
    }
}

- Larry

---

你怎么知道它会导致内存开销？ - Lasse V. Karlsen

任务管理器中的私有内存使用情况。当我正确处理SQLiteCommand时，就不会出现这种情况。这种行为也在这里看到。还有这里。顺便说一下，我对是否有解决方案很感兴趣 :) - Larry