SQL Server 與 .NET 效能調優全攻略：從連線池到批次處理

前言

SQL Server 與 .NET 同屬微軟生態，效能息息相關。優化資料庫效能不僅著眼於 T-SQL，更需從 .NET 應用程式層面著手。本文詳細解析關鍵效能指標、SQL 撰寫原則，並深入探討 .NET 端的連線池、批次處理、資料存取元件選擇等實戰技巧。

核心效能指標

• 回應時間：從發送請求到收到資料的總耗時。
• 吞吐量：單位時間內成功傳輸的資料量。
• 可擴展性：隨使用者增加，維持可接受回應時間與吞吐量的能力。

SQL 程式碼撰寫四大原則

1. 減少網路通訊：批次作業、減少往返。
2. 限制磁碟 I/O：善用索引、避免全表掃描。
3. 優化應用程式與驅動程式交互：預編譯、參數化。
4. 簡化請求：只取必要欄位、避免複雜邏輯下推。

.NET 程式優化關鍵技巧

1. 善用連線池

連線池緩存實體連線，避免重複建立昂貴的實體連線。

• 適用：應用伺服器、多使用者、資料庫記憶體充足。
• 不適用：頻繁重啟應用、單一使用者應用。
• .NET 預設啟用，連線字串加 Pooling=false 可關閉。

2. 高效建立與關閉連線

盡早關閉連線，避免等待 GC 回收造成記憶體壓力。使用 using 區塊確保釋放。

3. 關閉自動提交，改用手動交易控制

using (var tran = conn.BeginTransaction())
{
    // 多筆作業
    tran.Commit(); // 單次提交，減少磁碟 I/O 與網路往返
}

4. 視情況使用預編譯

• 重複執行同一 SQL → cmd.Prepare() 預編譯，省去重複解析優化。
• 單次執行 → 無需預編譯，否則反增一次網路往返。

5. 大量資料更新：參數陣列與批次處理

參數陣列：設定 cmd.ArrayBindCount，一次往返執行多筆參數化命令。

cmd.CommandText = "INSERT INTO employees VALUES (?, ?)";
cmd.ArrayBindCount = 100;
cmd.Parameters[0].Value = idArray;
cmd.Parameters[1].Value = nameArray;
cmd.ExecuteNonQuery(); // 僅 2 次網路往返

批次處理：將多條 SQL 合併為一個命令字串。

cmd.CommandText = "INSERT ...; INSERT ...; ..."; // 100 條
cmd.ExecuteNonQuery(); // 2 次網路往返

DataAdapter 批次更新：設定 UpdateBatchSize 打包命令。

sda.UpdateBatchSize = 5; // 每 5 條打包一次

6. 避免使用 CommandBuilder

CommandBuilder 執行時產生 SQL、分析結果集，效能低且並發受限。明確指定 Insert/Update/Delete 命令更佳。

7. 正確選擇 DataReader vs DataSet

• DataReader：唯讀、前向、大量資料、記憶體極小 → 首選。
• DataSet：需修改、隨機存取、跨表關聯 → 酌情使用，注意記憶體佔用。

8. 使用強型別 GetXXX 方法

避免 GetValue() 裝箱拆箱，直接呼叫 GetInt32()、GetString() 等。

9. DataSet 內搜尋資料優化

• 主鍵查詢 → DataTable.Rows.Find()（利用主鍵索引）。
• 非主鍵多次查詢 → 建立 DataView 並設定 Sort，使用 Find/FindRows。
• 單次查詢 → 直接 Select，建索引成本可能大於收益。

總結

SQL Server 與 .NET 效能優化是系統工程，兩端皆需精進。從連線池管理、批次處理、資料存取元件選型到索引設計，環環相扣，方能構建高效能、可擴展的資料驅動應用。