ASP.NET 大量 Excel 匯入去重優化：記憶體比對 + 批次寫入

效能瓶頸分析

原方案：逐筆讀 Excel → 查資料庫是否存在 → 存在更新/不存在插入。1 萬筆耗時 2 分鐘，主因在於：

• 資料庫往返 10,000 次（網路延遲 + 交易開銷）。
• 每次查詢觸發索引掃描/鎖定。
• 單筆執行無法利用批次優化。

優化策略：記憶體去重 + 批次合併

┌─────────────┐     1. 讀取 Excel 關鍵欄位到記憶體 (HashSet/Dictionary)
│  Excel 檔   │ ─────────────────────────────────────────────────────▶
└─────────────┘                                                      │
                                                                     ▼
┌─────────────┐     2. 一次性查詢資料庫現有關鍵鍵值 (SELECT key FROM table)
│  資料庫     │ ◀──────────────────────────────────────────────────────
└─────────────┘                                                      │
                                                                     ▼
┌─────────────┐     3. 記憶體中比對：分為 ToInsert / ToUpdate 兩批
│  記憶體比對  │ ─────────────────────────────────────────────────────▶
└─────────────┘                                                      │
                                                                     ▼
┌─────────────┐     4. 批次執行：BulkInsert / BulkUpdate (SqlBulkCopy / EF Core BulkExtensions)
│  批次寫入   │ ─────────────────────────────────────────────────────▶
└─────────────┘

關鍵程式碼 (C# + SqlBulkCopy)

// 1. 讀取 Excel (使用 NPOI / EPPlus)
var dtExcel = ExcelHelper.ReadToDataTable(filePath); // 包含所有欄位
var keyColumn = "ID"; // 業務主鍵

// 2. 取得 Excel 所有 Key
var excelKeys = dtExcel.AsEnumerable()
    .Select(r => r[keyColumn].ToString())
    .Distinct()
    .ToList();

// 3. 一次查詢資料庫現有 Key
var existingKeys = new HashSet<string>();
using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand($"SELECT {keyColumn} FROM TargetTable WHERE {keyColumn} IN ({string.Join(",", excelKeys.Select((_,i)=>"@p"+i))})", conn)) {
    for (int i=0; i<excelKeys.Count; i++) cmd.Parameters.AddWithValue($"@p{i}", excelKeys[i]);
    conn.Open();
    using (var reader = cmd.ExecuteReader())
        while (reader.Read()) existingKeys.Add(reader.GetString(0));
}

// 4. 分批
var dtInsert = dtExcel.Clone();
var dtUpdate = dtExcel.Clone();
foreach (DataRow row in dtExcel.Rows) {
    if (existingKeys.Contains(row[keyColumn].ToString()))
        dtUpdate.ImportRow(row);
    else
        dtInsert.ImportRow(row);
}

// 5. 批次寫入
using (var bulk = new SqlBulkCopy(connStr, SqlBulkCopyOptions.TableLock)) {
    bulk.DestinationTableName = "TargetTable";
    bulk.BatchSize = 5000;
    bulk.BulkCopyTimeout = 300;
    if (dtInsert.Rows.Count > 0) bulk.WriteToServer(dtInsert);
}

// 6. 批次更新 (使用 MERGE 或臨時表 + JOIN UPDATE)
if (dtUpdate.Rows.Count > 0) {
    // 將 dtUpdate 寫入暫存表 #TmpUpdate
    // 執行 MERGE: WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...
}

進階優化技巧

• 資料流式讀取： 使用 IDataReader 介面實作 ExcelDataReader，配合 SqlBulkCopy 實現「邊讀邊寫」，記憶體佔用恆定。
• 分區表/分批提交： 萬筆以上建議每 5,000 筆一批，避免交易日誌暴漲與鎖升級。
• 非叢集索引優化： 確保 keyColumn 有索引，查詢既有 Key 才能毫秒級完成。
• 異步管線： 生產者-消費者模式，讀取執行緒 + 寫入執行緒並行，進一步縮短總耗時。

預期效能提升

總結

大量資料匯入去重的核心原則是 「減少資料庫往返、利用記憶體比對、批次寫入」。將 10,000 次單筆操作合併為 2-3 次批次操作，配合 SqlBulkCopy / MERGE，可將匯入時間從分鐘級壓縮至秒級，並大幅降低資料庫負載。