分享高效能批量插入和大量刪除sql語句寫法

技術水平總能在扯皮和吹毛求疵中得到提高。如果從來不“求疵”，可能就不會知道if(str != "")不如if(str != string.Empty)高效、批量插入和刪除的sql語句是要那樣寫才執行最快、介面和抽象類別的區別不僅是語言層面、原來許可權管理是要這樣設計的、某個類那樣設計職責才更單一更易於擴充……

    本來前兩篇文章是學習cnblogs編輯控制項用的，看到跟貼的朋友詢問批量插入和大量刪除的問題，決定整理成文和大家分享。

    我們這裡討論的只是普通sql語句如何寫更高效，不考慮特殊的用檔案中轉等匯入方式，畢竟在代碼中調用sql語句或預存程序才更方便。

    大量刪除很簡單，大家可能都用過：

    DELETE FROM TestTable WHERE ID IN (1, 3, 54, 68)  --sql2005下運行通過

    當使用者在介面上不連續的選擇多項進行刪除時，該語句比迴圈調用多次刪除或多條delete語句中間加分號一次調用等方法都高效的多。

   

    本文重點講述的是批量插入的寫法：

    sql寫法：

    INSERT INTO TestTable SELECT 1, 'abc' UNION SELECT  2, 'bcd'  UNION SELECT 3, 'cde'   --TestTable表沒有主鍵，ID不是主鍵

    oracle寫法：

    INSERT INTO TestTable SELECT 1, 'abc' From daul UNION SELECT  2, 'bcd' From daul    --TestTable表沒有主鍵，ID不是主鍵

    曾經測試過，這種寫法插入1000條資料比迴圈調用1000次insert或1000條insert語句簡單疊加一次調用要高效得多，大概快20多倍（調試狀態不是太准）。其實很簡單，就用了個union（union all 也可以），但當時得出測試結果時還是很驚喜的。

    要得出這個結果需要兩個條件：

    1、表不能有主鍵或者主鍵是資料庫預設的（sql用自動遞增列，oracle用序列）

    2、組合sql語句時只能直接用字串串連，不能用參數化sql語句的寫法（就是在組合的sql中用@parm做預留位置，再給Command對象添加Parameter）

    以上兩條任意一條不滿足，效率的提高都不明顯。

    另外，sql語句的最大長度有限制，組合幾千條資料寫成一條insert語句，可能會超過上限，所以如果有5000條資料，可以一次insert 1000條，分5次寫完（不一定一次1000條最合適，有興趣的朋友可以研究）。

 

聲明：看了幾位朋友的評論後發現自己沒有敘述清楚

     上面的兩個條件並不是說這樣做就好，而是說當應用情境滿足這兩個條件時才適合用union的寫法

     當時用這種寫法時，是為瞭解決一個WinForm程式大量匯入手機號碼的問題，程式給自己人用，excel檔案也是可靠的，不需要考慮sql攻擊。還有一些情境是大量資料來源於通過Gprs網路連接的專用終端，總之資料來源是可靠的，還有就是資料量很大但不可能巨大。

 

 

PS：常常遇到一些小技巧或者值得注意的小細節，沒有記下來，要等下次再遇到時才想起來。現在下決心要養成好的工作習慣，留此文為證。

      能夠整理成文的要寫在項目組開發規範中，其它的至少要記錄下來。