C#:SQLite大量插入的效率问题

QLite大量插入的效率问题

http://www.cnblogs.com/rader/articles/1543760.html

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

1 2 3 4 5 6 7 8

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代码是不是比它快。下面是纯手工的数据库操作代码： 
 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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万条记录，而我的代码没有使用事务，而是每次提交。于是我改写了一下我的代码，也加上事务进行提交：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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。