挑戰極限：.NET + SQL Server 4 秒導入 100 萬筆 — 規則、現狀與正確姿勢

活動背景

社群發起「.NET + SQL Server 4 秒導入 100 萬筆」挑戰賽，規則嚴格：

• 平台：.NET (ADO.NET)，資料庫：SQL Server
• 單表單欄位：TableB(Value1 NVARCHAR(50))
• **禁用**預存程序、資料表值參數 (TVP) 等進階功能
• 資料量：100 萬筆 / 測試單元
• 硬體：2×2.0GHz CPU / 2GB RAM
• 繳交：無 Bug、無彩蛋、無病毒的完整代碼

為什麼這是個「不公平」的挑戰？

正確的「極速導入」標準姿勢 (生產級)

1. SqlBulkCopy + IDataReader (記憶體友善、流式處理)

public class CsvDataReader : IDataReader
{
    private readonly StreamReader _reader;
    private string[] _current;
    // 實作 IDataReader 介面... (逐行讀取 CSV yield return)
}

using (var conn = new SqlConnection(cs))
using (var bulk = new SqlBulkCopy(conn))
{
    bulk.DestinationTableName = "TableB";
    bulk.BatchSize = 100000;           // 批次大小
    bulk.BulkCopyTimeout = 0;          // 無限等待
    bulk.EnableStreaming = true;      // 大物件流式
    bulk.SqlRowsCopied += (s,e) => Console.WriteLine($"已寫入 {e.RowsCopied:N0} 列");
    
    conn.Open();
    // 關鍵：最小日誌記錄需滿足條件
    // 1. 資料庫處於 Simple/Bulk-Logged 模式
    // 2. 目標表無觸發器、無 CDC、無複寫
    // 3. 使用 TABLOCK (SqlBulkCopyOptions.TableLock)
    bulk.BulkCopyOptions = SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.CheckConstraints;
    
    using (var reader = new CsvDataReader(@"D:\Data\100wan.csv"))
        bulk.WriteToServer(reader);
}

2. 資料庫端必備設定 (事半功倍)

-- 1. 切換復原模式 (大量載入期間)
ALTER DATABASE TestDB SET RECOVERY SIMPLE; -- 或 BULK_LOGGED
GO

-- 2. 目標表：無索引、無約束、無觸發器 (載入後再建)
--    若有叢集索引，資料檔**必須依叢集鍵排序**，否則分頁分裂殺效能

-- 3. 檔案群組預增長 (避免自動增長卡頓)
ALTER DATABASE TestDB MODIFY FILE (NAME = TestDB_Data, SIZE = 2GB, FILEGROWTH = 512MB);
ALTER DATABASE TestDB MODIFY FILE (NAME = TestDB_Log, SIZE = 1GB, FILEGROWTH = 256MB);
GO

3. 硬體層面：TempDB 放 SSD、資料/記錄分離、啟用瞬間檔案初始化

• 授予 SQL 服務帳號 執行量維護工作 (SeManageVolumePrivilege) → 瞬間檔案初始化，資料檔擴容零填充。
• TempDB 多資料檔 (核心數/8) 放最快 NVMe。

實測基準 (參考值，現代硬體)

**結論**：在挑戰賽規則 (單執行緒、完整日誌、舊硬體) 下 4 秒極難；在**生產級最佳實踐**下，100 萬筆**遠低於 1 秒**是常態。

給挑戰者的建議

1. 用 SqlBulkCopy + TableLock + 自訂 IDataReader (避免 DataTable 佔記憶體)。
2. 預熱：先跑一次小批次讓 JIT 編譯、連線池建立、頁面載入緩存。
3. 關閉防毒軟體、關閉非必要服務、設定電源計畫「高效能」。
4. 測試前 DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS; (僅測試環境)。
5. **別為了挑戰而挑戰**——掌握 SqlBulkCopy 最佳實踐，才是解決真實 ETL/同步/遷移問題的鑰匙。

1. 上一篇文章： 不挑戰極限：VFP 導入 1000 萬筆資料僅需 5 秒 — 重新定義「資料庫效能」
2. 下一篇文章： SQL Server 分頁存儲過程效能大比拚：ROW_NUMBER() vs 傳統 NOT IN — 50 萬筆實測

關於SqlServer

SQL Server 警示建立完整指南：事件警示與效能條件警示實戰

警示機制簡介 SQL Server Agent 透過讀取 W

MS SQL Server 基礎教程：建立預存程序完整指南 (Enterprise Manager 與 T-SQL 双軌教學)

建立預存程序的三大構成要素 所有輸入參數與輸出參數 針對資

SQL Server 登錄審計實戰：Logon Trigger + BCP 記錄登入時間，含 DAC 緊急救援指南

需求 記錄所有 SQL Server 登入帳號與時間，防範竄

SQL Server 查詢結果匯出 Excel 完整指南：SSMS 精靈、BCP、OPENROWSET、CLR 与自動化存儲過程

將查詢結果匯出為 Excel 是 DBA、開發、業務分析師的