SQLite中批量插入/大批量插入速度慢

Question

我试图将csv文件中的数据导入到sqlite表中。我的测试数据只有大约8Mb（50,000行），需要约15秒。然而，生产数据几乎有400Mb，需要很长时间（至少30分钟以上，我放弃等待）。

经过多番研究，我发现需要在单个事务中执行插入操作（这使我获得了15秒的导入时间，非常好的建议！:)）。所以这不是问题。（据我所知）

我还使用“ExecuteNonQuery() on a parameterized INSERT statement”，如此Robert Simpson post所述 - 以及其他许多变化形式。

我之前只是使用TextReader.ReadLine()和String.Split('\t')，后来我在某个地方读到ReadLine()由于磁盘读取次数过多而变慢，因此我研究了读取缓冲流，并找到了这个csv reader。但性能仍然没有明显改善。

因此，我注释掉了插入循环的核心部分，读取几乎立即发生 - 所以我确定问题出在插入上。我尝试了许多创建参数化查询+单个事务的变化形式，但几乎都有相似的结果。

这是我的代码的常规版本。非常感谢您的帮助，这让我抓狂！我即将尝试导入数据集并插入？...

using (TextReader tr = File.OpenText(cFile))
{                       
    using (SQLiteConnection cnn = new SQLiteConnection(connectionString))
    {
        string line;
        string insertCommand = "INSERT INTO ImportTable VALUES (@P0,@P1,@P2,@P3,@P4)";

        cnn.Open();
        SQLiteCommand cmd = new SQLiteCommand("begin", cnn);
        cmd.ExecuteNonQuery();

        cmd.CommandText = insertCommand;

        while ((line = tr.ReadLine()) != null)
        {
            string[] items = line.Split('\t');

            cmd.Parameters.AddWithValue("@P0", items[0]);
            cmd.Parameters.AddWithValue("@P1", items[1]);
            cmd.Parameters.AddWithValue("@P2", items[2]);
            cmd.Parameters.AddWithValue("@P3", items[3]);
            cmd.Parameters.AddWithValue("@P4", items[4]);
            cmd.ExecuteNonQuery();
        }
        cmd.CommandText = "end";
        cmd.ExecuteNonQuery(); 
    }              
}

更新：我刚刚尝试了使用带参数的插入（只是硬编码一些值），少于5秒... 仍然不如我看到的文章快...

另外，我正在运行一台Core2 Duo（3Ghz）配备2G内存，XP系统。

- Matt C

---

你确定 begin 和 end 命令在起作用吗？如果你把它们去掉，执行时间有变化吗？ - Daniel Hilgarth

谢谢。是的，它们似乎在起作用。我现在正在尝试不使用它们，但已经花费了至少10分钟（原本只需要15秒）...我还尝试了transaction = connection.BeginTransaction()/.transaction.commit的方式（性能与上述方式相同）。 - Matt C

好的。只是想确认一下。因为我本来要建议使用事务(Transaction)或者事务范围(TransactionScope)...但那并不能解决你的问题... - Daniel Hilgarth

那个程序两个小时后还在运行... - Matt C

刚刚发现了一些你可以尝试的东西：http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/ - grimmig

Answer 1

所以我认为我已经解决了这个问题 - 或者至少找到了一个解决方案。

由于我已经用尽了所有的代码选项（而且似乎没有人对我的代码有答案/问题），我决定问题可能在数据库本身...

我已经在SQLite Manager Firefox插件中创建了我的数据库和表。

所以我从命令行重新创建了一切，BOOM！我的导入时间只需要几秒钟！

我知道它无法处理64位整数时会出现问题（但只使用TEXT数据类型）。也许SQLite Manager使用不同的SQLite引擎与.Net版本存在问题？我不知道。

我的下一步可能是实际上从我的应用程序中创建db +表，而不是预先准备好它们...但我对性能感到相当满意，所以这不是优先考虑的事情。

- Matt C