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秒，後來分析應該是內部使用了批量插入操作），看來是My Code寫的有問題。仔細閱讀了作者給的例子之後發現，我們代碼的差別主要在於事務的使用上。作者給的例子使用事務一次性提交10萬條記錄，而My Code沒有使用事務，而是每次提交。於是我改寫了一下My Code，也加上事務進行提交：

            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。