LINQ to SQL 大量刪除與清空資料表效能優化實戰

專案中需刪除資料表所有資料或依條件大量刪除 (如 StockCode = 'XXX')，初期使用 LINQ to SQL 內建 DeleteAllOnSubmit 發現效能極差：30 萬筆資料刪除耗時難以接受。本文分析原因並提供高效替代方案。

問題重現：DeleteAllOnSubmit 效能陷阱

// 典型寫法：先查詢再刪除
var maList = ctx.T_Info_MAs.Where(c => c.StockCode == stockcode).ToList();
ctx.T_Info_MAs.DeleteAllOnSubmit(maList);
ctx.SubmitChanges();

效能殺手分析

1. 雙倍資料傳輸：ToList() 將 30 萬筆實體完整載入記憶體 (SELECT *)，再由 SubmitChanges() 產生 30 萬條個別 DELETE 陳述式送回資料庫。
2. 追蹤開銷：DataContext 對每個實體建立追蹤快照、身分識別快取、變更集管理，記憶體與 CPU 雙重壓力。
3. 網路往返：若非批次更新模式，每條 DELETE 獨立往返，延遲累積驚人。

解決方案：ExecuteCommand 直通 SQL

條件刪除

// 直接下發單條 DELETE 陳述式，資料庫端完成過濾與刪除
ctx.ExecuteCommand("DELETE FROM T_Info_MA WHERE [STOCKCODE] = {0}", stockcode);

• 參數化寫法 {0} 自動防注入，等同 sp_executesql 參數化。
• 單一 SQL 陳述式，資料庫引擎內部最佳化 (索引搜尋、頁面鎖定、極小化記錄)。
• 無實體物質化、無變更追蹤、無網路往返迴圈。

清空整張資料表

// TRUNCATE TABLE：極速、最少記錄、重設身分識別種子
ctx.ExecuteCommand("TRUNCATE TABLE T_Info_MA");

進階：批次刪除大量資料 (避免記錄檔暴漲)

若資料表極大 (億級)，單次 DELETE 或 TRUNCATE 可能導致交易記錄檔暴漲或鎖定升級。分批刪除範本：

const int BatchSize = 50000;
int deleted;
do
{
    deleted = ctx.ExecuteCommand(
        "DELETE TOP ({0}) FROM T_Info_MA WHERE [STOCKCODE] = {1}",
        BatchSize, stockcode);
} while (deleted > 0);

• TOP (N) 限制單次交易影響範圍。
• 迴圈直到無資料刪除為止。
• 可搭配 WAITFOR DELAY '00:00:00.100' 降低鎖定衝突。

何時仍需使用 DeleteAllOnSubmit？

• 資料量極小 (百筆內)
• 需要觸發實體層級邏輯 (如 OnDeleting partial 方法、領域事件)
• 涉及複雜關聯刪除 (Cascade Delete 需由 ORM 協調)

總結

LINQ to SQL 設計初衷是「物件導向資料操作」，大量 DML 操作並非其強項。**大量刪除/清表請毫不猶豫使用 ExecuteCommand 直通 T-SQL**，兼顧效能與簡潔。記住：TRUNCATE TABLE 是清空資料表的黃金標準；條件刪除用參數化 DELETE；超大資料量採分批策略。