C# SQLite 删除行非常慢

Question

我创建了一个脚本来删除本地 sqlite 数据库中大约 180 行。这是我的代码:

await Task.Run(() =>
{
    sql_con.Open();

    using (var trans = sql_con.BeginTransaction())
    {
        foreach (int i in toDelete)
        {
            cmd = String.Format("DELETE FROM document WHERE docid = '{0}'", i);
            sql_cmd = new SQLiteCommand(cmd, sql_con);
            sql_cmd.ExecuteNonQuery();
        }
        trans.Commit();
    }

    sql_con.Close();
}).ConfigureAwait(false);

我用秒表和 Debug.WriteLine 对其进行了测试...随机需要大约 2-12 秒。但它应该只需要大约几毫秒。 docid列被标记为索引列，在数据库中不超过500行左右。

如果我使用相同的脚本插入内容，大约只需要几毫秒。

有什么想法吗？

编辑1:这是我的 PRAGMA

PRAGMA AUTO_VACUUM=0;
PRAGMA synchronous=OFF;
PRAGMA COUNT_CHANGES=OFF;
PRAGMA TEMP_STORE=MEMORY;

编辑2:这是我的数据库脚本:

CREATE TABLE IF NOT EXISTS document (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  docid TEXT NOT NULL,
  key TEXT NOT NULL,
  value TEXT,
  UNIQUE (docid, key)
);

CREATE INDEX IF NOT EXISTS docid_index ON document (docid);

编辑3:这是我最新的脚本，包含所有更改:

await Task.Run(() =>
{
    Stopwatch stopwatch = Stopwatch.StartNew();
    sql_con.Open();
    Debug.WriteLine("After open {0} seconds.", stopwatch.Elapsed.TotalSeconds);
    stopwatch.Restart();

    using (var trans = sql_con.BeginTransaction())
    {
        sql_cmd = sql_con.CreateCommand();
        sql_cmd.Transaction = trans;
        sql_cmd.CommandText = "DELETE FROM document WHERE docid = '@docid';";

        foreach (int i in toDelete)
        {
            sql_cmd.Parameters.AddWithValue("@docid", i);
            sql_cmd.ExecuteNonQuery();
        }

        Debug.WriteLine("After execute nonQuery {0} seconds.", stopwatch.Elapsed.TotalSeconds);
        stopwatch.Restart();
        trans.Commit();
        Debug.WriteLine("After commit {0} seconds.", stopwatch.Elapsed.TotalSeconds);
        stopwatch.Restart();
    }

    sql_con.Close();
    Debug.WriteLine("After close {0} seconds.", stopwatch.Elapsed.TotalSeconds);
    stopwatch.Stop();

}).ConfigureAwait(false);

Answer 1

尝试将所有内容一起发送

await Task.Run(() =>
{
    sql_con.Open();

    using (var trans = sql_con.BeginTransaction())
    {
        foreach (int i in toDelete)
        {
            cmd += String.Format("DELETE FROM document WHERE docid = '{0}';\n", i);             
        }
        sql_cmd = new SQLiteCommand(cmd, sql_con);
        sql_cmd.ExecuteNonQuery();
        trans.Commit();
    }

    sql_con.Close();
}).ConfigureAwait(false);