萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 2005 批量更新效能優化:PreparedStatement 與 JDBC 驅動陷阱

Java 批量更新 SQL Server 2005 的正確姿勢

數據割接專案中遭遇批量更新效能與穩定性問題,經排查歸因於 Statement 類別選擇錯誤與 JDBC 驅動版本不匹配。記錄避坑指南。

錯誤示範:Statement.addBatch

Statement stmt = conn.createStatement();
stmt.addBatch("UPDATE t SET c=1 WHERE id=1");
stmt.addBatch("UPDATE t SET c=2 WHERE id=2");
// ... 成千上萬條不同 SQL
stmt.executeBatch(); // 每條都需編譯,效能極差

缺點: 每條 SQL 獨立編譯,無法重用執行計畫,網路往返開銷大,大批量時極易超時或 OOM。

正解:PreparedStatement + 參數化批次

String sql = "UPDATE t SET col1=?, col2=? WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sql);

for (DataRecord rec : batchList) {
    ps.setInt(1, rec.getCol1());
    ps.setString(2, rec.getCol2());
    ps.setLong(3, rec.getId());
    ps.addBatch();
}

int[] results = ps.executeBatch(); // 單次網路往返,預編譯重用

優勢: 僅編譯一次,參數綁定極快,單次網路往返提交整批,效能提升 10-100 倍。

致命坑:JDBC 驅動版本

舊版 com.microsoft.jdbc.sqlserver.SQLServerDriver(SQL Server 2000 驅動)不支援 真正的批量更新,executeBatch() 實際仍逐條發送,且易拋出:

SQLServerException: sp_cursoropen/sp_cursorprepare: 
The statement parameter can only be a single statement or a stored procedure...

必須升級至 Microsoft JDBC Driver 1.2+

-- Maven 依賴範例

    com.microsoft.sqlserver
    mssql-jdbc
    12.2.0.jre11 

連線字串最佳實踐

jdbc:sqlserver://127.0.0.1:1433;databaseName=fax;selectMethod=cursor;
sendStringParametersAsUnicode=false;applicationName=BatchImport;
  • selectMethod=cursor:大結果集流式讀取,避免 OOM。
  • sendStringParametersAsUnicode=false:非 Unicode 資料庫可減少網路傳輸量。
  • applicationName:便於 SQL Server Profiler 追蹤來源。

批次大小調優建議

  • 預設批次 1000-5000 行,根據行寬與記憶體調整。
  • 超大批次(>10萬)建議分段提交,避免鎖升級與記憶體壓力。
  • 搭配 SET NOCOUNT ON 減少網路封包。

替代方案:Table-Valued Parameters (TVP)

SQL Server 2008+ 支援傳遞資料表參數,單次呼叫存儲過程完成批量 UPSERT,效能更強、原子性更好。

-- 資料庫端建立類型
CREATE TYPE dbo.BatchUpdateType AS TABLE (Id BIGINT, Col1 INT, Col2 NVARCHAR(50));

-- Java 端使用 Struct/Array 傳遞(需驅動 4.2+ 支援)
copyright © 萬盛學電腦網 all rights reserved