SQLite大量插入的效率问题

这两天再做一个数据转换的程序，讲原来用二进制形势存储的数据转换到SQLite数据库中去。记录总共有1千万多点。开发使用的是.net framework 3.5 sp1的环境，所以直接使用了EntityFramework方便数据库的操作。EntityFramework倒是省了一些事情，可是效率上让人无法忍受。整个程序运行一次花了40多分钟。利用EntityFramework，首先从数据库生成模型，然后我这样来保存数据：

using (GameSetEntities gse = new GameSetEntities(CreateConnection()))
{
    foreach (var ticket in tickets)
    {
        gse.AddToShuffledTicketSet(ticket);
    }
    gse.SaveChanges(true);
}

Ticket是我要插入数据库中的记录。一千多万条记录，我是分成5000条一批进行处理的。5000条记录先被添加到GameSetEntities（DataContext）中，然后进行提交。

利用StopWatch，我记录了这5000条记录插入数据库所消耗的时间，平均是1.1秒。（2080 * 1.1）/60 大约是36分钟，也就是说整个程序的运行时间中有80%多的时间是花在了数据库的插入操作上。插入5000条记录平均花费1.1秒，这个速度实在太慢了。我开始怀疑是不是EntityFramework效率问题？既然这样那就比比看吧，看看纯粹的ado.net代码是不是比它快。下面是纯手工的数据库操作代码：
 

using (DbConnection conn = DbProviderFactories.GetFactory("System.Data.SQLite").CreateConnection())
            {
                conn.ConnectionString = "Data Source = " + m_datasourceFile + ";Version = 3";
                using (SQLiteCommand insertRngCmd = (SQLiteCommand)conn.CreateCommand())
                {
                    insertRngCmd.CommandText = @"INSERT INTO shuffled_tickets (ticket_idx, seed, win_credits, [timestamp], redeemed, prog_levels)
                                                    VALUES  (@ticket_idx, @seed, @win_credits, @timestamp, @redeemed, @prog_levels)";
                    conn.Open(); 

                    foreach (var ticket in tickets)
                    {
                        insertRngCmd.Parameters.AddWithValue("@ticket_idx", ticket.ticket_idx);
                        insertRngCmd.Parameters.AddWithValue("@seed", ticket.seed);
                        insertRngCmd.Parameters.AddWithValue("@win_credits", ticket.win_credits);
                        insertRngCmd.Parameters.AddWithValue("@timestamp", ticket.timestamp);
                        insertRngCmd.Parameters.AddWithValue("@redeemed", ticket.redeemed);
                        insertRngCmd.Parameters.AddWithValue("@prog_levels", ticket.prog_levels); 

                        insertRngCmd.ExecuteNonQuery();
                    }
                }
            }

这样的代码一运行，发现速度更慢了，5000记录居然要20秒的时间……看来问题不是在这里。

Google一番之后，在Sqlite.net ADO Provider的论坛里发现这篇文章：Fastest Bulk Inserts.在最后一个例子“Fastest universal way to insert data using standard ADO.NET constructs”中作者提到“100,000 inserts on my machine in 1.4 seconds”--10万条记录插入耗时1.4秒。我才5000条不应该需要20秒那么慢的（用EntityFramework需要1.1秒，后来分析应该是内部使用了批量插入操作），看来是我的代码写的有问题。仔细阅读了作者给的例子之后发现，我们代码的差别主要在于事务的使用上。作者给的例子使用事务一次性提交10万条记录，而我的代码没有使用事务，而是每次提交。于是我改写了一下我的代码，也加上事务进行提交：

            using (DbConnection conn = DbProviderFactories.GetFactory("System.Data.SQLite").CreateConnection())
            {
                conn.ConnectionString = "Data Source = " + m_datasourceFile + ";Version = 3";
                
                using (SQLiteCommand insertRngCmd = (SQLiteCommand)conn.CreateCommand())
                {
                    insertRngCmd.CommandText = @"INSERT INTO shuffled_tickets (ticket_idx, seed, win_credits, [timestamp], redeemed, prog_levels)
                                                    VALUES  (@ticket_idx, @seed, @win_credits, @timestamp, @redeemed, @prog_levels)";
                    conn.Open();
                    var transaction = conn.BeginTransaction();   

                    foreach (var ticket in tickets)
                    {
                        insertRngCmd.Parameters.AddWithValue("@ticket_idx", ticket.ticket_idx);
                        insertRngCmd.Parameters.AddWithValue("@seed", ticket.seed);
                        insertRngCmd.Parameters.AddWithValue("@win_credits", ticket.win_credits);
                        insertRngCmd.Parameters.AddWithValue("@timestamp", ticket.timestamp);
                        insertRngCmd.Parameters.AddWithValue("@redeemed", ticket.redeemed);
                        insertRngCmd.Parameters.AddWithValue("@prog_levels", ticket.prog_levels);

                        insertRngCmd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }

结果5000条记录的插入时间由原来的1.1秒变为0.09秒，这是一个非常大的提升。

但是为什么加上一个事务之后的差别这么大呢？我翻了翻Sqlite的文档，“Database Speed Comparison”里有解释：

在“Test1：1000 INSERTS”下面有一句话：……“In this test, each SQL statement is a separate transaction so the database file must be opened and closed and the cache must be flushed 1000 times”……

在“Test 2:25000 INSERT in a transaction”下面有另外一句话：……“When all the INSERTs are put in a transaction, SQLite no longer has to close and reopen the database or invalidate its cache between each statement. ”……

从上面两句话看来，没有事务的时候，SQLite的插入操作使用了太多的IO操作，而是用事务的话，只需要一次IO。