Sqlite大資料寫入效能最佳化

標籤：ons   cmd   exception   交易處理   res   先行編譯   傳值   public   lex   

　　眾所周知，Sqlite是一個輕量級的資料庫，僅僅需要一個exe檔案就能運行起來。在處理本機資料上，我比較喜歡選擇使用它，不僅是因為他與sql server有著比較相近的文法，還因為它不需要安裝，僅需要通過命令列就能啟動了，而且他在處理大資料時，效能比sql server好很多，好吧這裡不繼續爭論效能優劣。

　　首先，這次的問題是在一次項目中遇到的，項目要求能大量匯入資料，而且由於項目性質（輕便，本地化），所以我選擇sqlite來存放資料。

　　第一版代碼：

1 StringBuilder sql = new StringBuilder();2 foreach (DataRow dr in dt.Rows)3 {4     sql.Append("INSERT INTO Info (Name,Code) VALUES(‘"+dr[0]+"‘,‘"+dr[1]+"‘) \r\n");    5 }6 sqlHelper.SqliteHelper.ExecuteNonQuery( sql.ToString(), CommandType.Text);

View Code

　　從上面的代碼看來，我沒有進行任何最佳化工作，我用這種方式插入了1萬條資料用了1分多鐘，可見最佳化的重要性（百萬級資料跑起來...不敢想象）。

　　開啟事務：

1 StringBuilder sql = new StringBuilder();2 sql.Append("BEGIN;");//開啟事務3 foreach (DataRow dr in dt.Rows)4 {5      sql.Append("INSERT INTO Info (Name,Code) VALUES(‘"+dr[0]+"‘,‘"+dr[1]+"‘) \r\n");    6 }7 sql.Append("COMMIT;");//提交事務8 sqlHelper.SqliteHelper.ExecuteNonQuery( sql.ToString(), CommandType.Text);

View Code

      由於sqlite的資料操作實質上是對於其資料檔案的IO操作，頻繁的插入資料會導致檔案IO經常開閉，非常損耗效能能。事務作用便是使資料先緩衝在系統中，提交事務時便提交所有的更改到資料檔案，此時資料檔案的IO只需要開閉一次，且避免了長期佔用檔案IO所導致效能低下的問題。此時，開啟交易處理後效能上雖然有了大幅度的提升，但是結果仍舊不理想，哪還有什麼在影響著效能呢？下面嘗試開啟預先處理來解決問題。

　　開啟預先處理：

 1  public static int ExecuteNonQuery(string commandText, CommandType commandType) 2         { 3             int result = 0; 4             if (commandText == null || commandText.Length == 0) 5                 throw new ArgumentNullException("sql語句為空白"); 6             using (SQLiteConnection con = new SQLiteConnection(connectionString)) 7             { 8                 SQLiteCommand cmd = new SQLiteCommand(); 9                 cmd.Connection = con;10                 cmd.CommandText = commandText;11                 cmd.Prepare();//開啟預先處理12                 try13                 {14                     result = cmd.ExecuteNonQuery();15                 }16                 catch (Exception ex)17                 {18                     throw ex;19                 }20             }21             return result;22         }23                     

View Code

　　預先處理的原理就是將一條語句先先行編譯到資料庫，下次再次執行相同的語句時，就不用再次編譯，節省了大量的時間。由此看來，代碼似乎還沒有最佳化完成。就第一版代碼來說，由於批量插入的資料不盡相同，所以資料庫會多次編譯插入語句，效能會損耗非常多，也就造成插入需要的時間會比較多。有沒有一條語句是可以迴圈使用的？答案是有的，使用參數化傳值，就能使每一次的插入的sql語句都是相同的。

　　參數化（資料庫協助類那邊我就不寫了）：

 1 StringBuilder sql = new StringBuilder(); 2 SQLiteParameter[] sp = new SQLiteParameter[2]; 3 foreach (DataRow dr in dt.Rows) 4 { 5 sql.Clear(); 6 sql.Append("INSERT INTO Info (Name,Code) VALUES(@Name,@Code) \r\n");   7 sps[0] = new SQLiteParameter("@p1", dr["Name"]); 8 sps[1] = new SQLiteParameter("@p2", dr["Code"]); 9 sqlHelper.SqliteHelper.ExecuteNonQuery(sql.ToString(),sp, CommandType.Text);  10 }

View Code

　　經過一系列最佳化處理後，插入1萬條資料只需要不到1秒，效能得到了極大的提升。

　　另外，本人新手，如有不對，望不吝指教。

Sqlite大資料寫入效能最佳化