SqLite C# 更新速度极慢

Question

我真的很难解决这个问题。当我使用以下代码更新大量记录的数据库时，它运行速度非常慢。我有 500,000 条记录要更新，需要将近一个小时。在此操作期间，日志文件增长缓慢，主 SQLite db3 文件几乎没有变化 - 这是正常的吗？

只有

当我有大量数字或记录要更新时，该操作似乎才是一个问题 - 它几乎立即在较少数量的记录上运行。

在此代码运行之前，会对数据库执行一些其他操作，因此它们是否会占用数据库？我试图确保所有其他连接都正确关闭。

感谢您的任何建议

using (SQLiteConnection sqLiteConnection = new SQLiteConnection("Data Source=" + _case.DatabasePath))
{
    sqLiteConnection.Open();
    using (SQLiteCommand sqLiteCommand = new SQLiteCommand("begin", sqLiteConnection))
    {
        sqLiteCommand.ExecuteNonQuery();
        sqLiteCommand.CommandText = "UPDATE CaseFiles SET areaPk = @areaPk, KnownareaPk = @knownareaPk WHERE mhash = @mhash";
        var pcatpk = sqLiteCommand.CreateParameter();
        var pknowncatpk = sqLiteCommand.CreateParameter();
        var pmhash = sqLiteCommand.CreateParameter();
        pcatpk.ParameterName = "@areaPk";
        pknowncatpk.ParameterName = "@knownareaPk";
        pmhash.ParameterName = "@mhash";
        sqLiteCommand.Parameters.Add(pcatpk);
        sqLiteCommand.Parameters.Add(pknowncatpk);
        sqLiteCommand.Parameters.Add(pmhash);
        foreach (CatItem CatItem in _knownFiless)
        {
            if (CatItem.FromMasterHashes == true)
            {
                pcatpk.Value = CatItem.areaPk;
                pknowncatpk.Value = CatItem.areaPk;
                pmhash.Value = CatItem.mhash; 
            }
            else
            {
                pcatpk.Value = CatItem.areaPk;
                pknowncatpk.Value = null;
                pmhash.Value = CatItem.mhash; 
            }
            sqLiteCommand.ExecuteNonQuery();
        }
        sqLiteCommand.CommandText = "end";
        sqLiteCommand.ExecuteNonQuery();
        sqLiteCommand.Dispose();
        sqLiteConnection.Close();
    }
    sqLiteConnection.Close();
}

Answer 1

首先要确保你在mhash上有一个索引。将命令分组到批处理中。使用多个线程。

或 [插入]

将记录批量导入临时表。在 mhash 列上创建索引。执行单个更新语句以更新记录。

Answer 2

您需要

将所有内容包装在事务中，否则我相信SQLite会为每次更新创建并提交一个...因此缓慢。您清楚地知道查看您的代码，但我不确定使用"开始"和"结束"命令在这里获得相同的结果，您可能会在开始和结束时得到空事务，而不是一个包装所有内容。尝试这样的事情以防万一：

  using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
  {
    using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
    {
      SQLiteParameter myparam = new SQLiteParameter();
      mycommand.CommandText = "YOUR QUERY HERE";
      mycommand.Parameters.Add(myparam);
      foreach (CatItem CatItem in _knownFiless)
      {
        ...
        mycommand.ExecuteNonQuery();
      }
    }
    mytransaction.Commit();
  } 

Answer 3

这部分肯定是你的问题。

foreach (CatItem CatItem in _knownFiless)
{
....
     sqLiteCommand.ExecuteNonQuery();
}

您正在循环 List（？） 并针对数据库执行查询。这不是一个好办法。因为数据库调用非常昂贵。因此，您可以考虑使用另一种方法来更新这些项目。

Answer 4

SQL 代码似乎没问题。C# 代码没有错，但它有一些冗余（不需要显式关闭/释放，因为您已经在使用using）。

_knownFiless上有一个 for 循环（打算用双 s？），可以缓慢运行吗？在针对数据库的 for 循环中运行查询是不寻常的，而应使用相应的参数集创建查询。考虑到（特别是没有哈希索引）您将执行 n * m 个操作（n 是 for 循环的运行计数，m 是表大小）。

考虑到 m 大约是 500k，假设 m = n，您将获得 250,000,000,000 次操作。这很可能持续一个小时。

据我所知，以前的连接或操作应该没有影响。

还应确保数据库的内部结构不会导致问题。是否存在受此操作影响的复合索引？任何外键/复杂约束？