您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 批次更新效能優化：PreparedStatement、JDBC 驅動與參數設定

編輯：SQL Server 2005

批次更新兩種 API 比較

特性	Statement	PreparedStatement
SQL 預編譯	否	是（一次編譯多次執行）
參數綁定	不支援	支援 `setXxx()`
批次加入	`addBatch(sql)`	`addBatch()`（使用已綁定參數）
適用場景	少量、SQL 結構不固定	大量、相同 SQL 結構
效能	低	高（推薦）

PreparedStatement 批次更新標準範式

String sql = "UPDATE tb SET col1=?, col2=? WHERE id=?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (Data d : dataList) {
        ps.setString(1, d.getCol1());
        ps.setInt(2, d.getCol2());
        ps.setLong(3, d.getId());
        ps.addBatch();
    }
    int[] results = ps.executeBatch();  // 回傳每筆影響列數
    // 檢查 results 陣列確認執行狀態
}

關鍵效能參數

rewriteBatchedStatements=true（MySQL/部分驅動）：將多條批次重寫為單條多值 `INSERT ... VALUES (...),(...)`。SQL Server JDBC 4.2+ 亦支援類似優化。
sendStringParametersAsUnicode=false：若資料庫非 Unicode，避免隱含轉換。
批次大小：建議 500~2000 筆分批提交，避免記憶體/記錄檔暴增。
自動認可：`conn.setAutoCommit(false)` 手動控制交易，批次結束 `commit()`。

JDBC 驅動版本陷阱

舊版驅動 (com.microsoft.jdbc.sqlserver.SQLServerDriver, 2000 時代) 不支援批次更新，會拋出：

SQLServerException: sp_cursoropen/sp_cursorprepare: 該語句參數只能是一個批或帶有單個 SELECT 語句的存儲過程...

必須升級至 Microsoft JDBC Driver 4.0+ (sqljdbc4.jar 以上)

Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
URL: jdbc:sqlserver://127.0.0.1:1433;databaseName=myDB;selectMethod=cursor;

常見錯誤與解決

錯誤現象	原因	解決
執行極慢/卡住	自動認可每筆寫入記錄檔	關閉自動認可，分批手動 commit
OutOfMemoryError	批次過大累積在客戶端緩衝區	分批 1000 筆執行 executeBatch()
死鎖/鎖逾時	長交易持有排他鎖	縮短交易範圍、加索引減少掃描、考慮 ROWLOCK 提示
參數資料類型不符	setXxx 與 DB 欄位型別不匹配	嚴格對應：setString→VARCHAR, setBigDecimal→DECIMAL 等

替代方案：Table-Valued Parameter (TVP)

SQL Server 2008+ 推薦使用 TVP 替代批次 PreparedStatement：

-- DB 端建立類型
CREATE TYPE dbo.IdValueTable AS TABLE (Id BIGINT, Val NVARCHAR(100))

-- Java 端
SQLServerDataTable tvp = new SQLServerDataTable();
tvp.addColumnMetadata("Id", Types.BIGINT);
tvp.addColumnMetadata("Val", Types.NVARCHAR);
for (Data d : list) tvp.addRow(d.getId(), d.getVal());

try (CallableStatement cs = conn.prepareCall("{call usp_BatchUpdate (?)}")) {
    cs.setStructured(1, "dbo.IdValueTable", tvp);
    cs.execute();
}

TVP 為單次往返、伺服器端集合操作、型別安全、效能最優。