SQLite性能优化

Question

我有一个file.sql文件，其中包含20,000个插入命令。

.sql文件的示例：

INSERT INTO table VALUES (1,-400,400,3,154850,'文本',590628,'TEXT',1610,'TEXT',79);

INSERT INTO table VALUES (39,-362,400,3,111659,'Text',74896,'TEXT',0,'TEXT',14);

我使用以下代码创建了一个内存SQLite数据库，并将值提取到其中，然后计算经过的时间。

using (var conn = new SQLiteConnection(@"Data Source=:memory:"))
{
    conn.Open();

    var stopwatch = new Stopwatch();
    stopwatch.Start();

    using (var cmd = new SQLiteCommand(conn))
    {
        using (var transaction = conn.BeginTransaction())
        {

                cmd.CommandText = File.ReadAllText(@"file.sql");
                cmd.ExecuteNonQuery();

            transaction.Commit();
        }
    }

    var timeelapsed = stopwatch.Elapsed.TotalSeconds <= 60
                          ? stopwatch.Elapsed.TotalSeconds + " seconds"
                          : Math.Round(stopwatch.Elapsed.TotalSeconds/60) + " minutes";
    MessageBox.Show(string.Format("Time elapsed {0}", timeelapsed));
    conn.Close();
}

我尝试过的事情：

1. 使用文件数据库而不是内存数据库。
2. 使用BEGIN TRANSACTION和COMMIT TRANSACTION [如代码所示]。
3. 使用Firefox扩展程序SQLite Manager测试脚本是否导致速度变慢；然而，令我惊讶的是，用我的代码处理的相同20,000行数据只需在4毫秒内从数据库中提取！
4. 使用PRAGMA synchronous = OFF和PRAGMA journal_mode = MEMORY。
5. 向.sql文件的开头和结尾添加BEGIN TRANSACTION和COMMIT TRANSACTION。

正如SQLite文档所说：SQLite每秒可以处理50,000个命令。这是真实的，我通过SQLite Manager [如第三个尝试中所述]确保了这一点；但是，我需要4分钟左右才能完成20,000个命令，这表明存在问题。

问题：我面临的问题是什么，为什么执行非常缓慢？！

- Roman Ratskey

---

你的代码执行需要多久时间？ - Sebastian

尝试使用相同的延迟。 - Roman Ratskey

我的应用程序中只需要4分钟。 - Roman Ratskey

当我尝试在Firefox的扩展程序SQLite Manager中执行时，仅需要4毫秒。 - Roman Ratskey

@Sebastian：我不相信这个说法，即使是真的，当在Firefox中使用SQLite Manager时，它怎么可能只需要4毫秒就完成了呢？ - Roman Ratskey

Answer 1

SQLite.Net文档建议使用以下结构进行事务处理

using (SqliteConnection conn = new SqliteConnection(@"Data Source=:memory:")) 
{
  conn.Open();
  using(SqliteTransaction trans = conn.BeginTransaction())
  {
    using (SqliteCommand cmd = new SQLiteCommand(conn))
    {
      cmd.CommandText = File.ReadAllText(@"file.sql");
      cmd.ExecuteNonQuery();
    }
    trans.Commit();
  }
  con.Close();
}

- zeFrenchy

Answer 2

你能否将文本文件内容进行操作，变成以下这种形式：

INSERT INTO table (col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, col11)
SELECT 1,-400,400,3,154850,'Text',590628,'TEXT',1610,'TEXT',79
UNION ALL
SELECT 39,-362,400,3,111659,'Text',74896,'TEXT',0,'TEXT',14
;

也许可以尝试将它们分成100组进行初步测试。

http://sqlite.org/lang_select.html

SqlLite似乎支持UNION ALL语句。

- granadaCoder

---

col01，等等，我当然会放入真实的列名。这些只是我的占位符。 - granadaCoder