分享方式:


使用大量複製 API 執行批次插入作業

下載 JDBC 驅動程式

Microsoft JDBC Driver for SQL Server 9.2 版和更新版本支援使用大量複製 API 進行批次插入作業。 此功能可讓使用者在執行批次插入作業時,啟用驅動程式來執行大量複製操作。 此驅動程式的目的是要改善效能,同時插入與驅動程式定期執行批次插入作業相同的資料。 驅動程式會剖析使用者的 SQL 查詢,利用大量複製 API 代替一般的批次插入作業。 以下設定是啟用大量複製 API 以使用批次插入功能的各種方式,以及其限制清單。 此頁面也包含一個小範例程式碼,示範用法和效能增加。

此功能僅適用於 PreparedStatement 和 CallableStatement 的 executeBatch()executeLargeBatch() API。

必要條件

啟用大量複製 API 以進行批次插入的必要條件。

  • 查詢必須是插入查詢 (查詢可能包含註解,但查詢必須以 INSERT 關鍵字開頭,此功能才會生效)。

啟用大量複製 API 進行批次插入

有三種方式可啟用大量複製 API 進行批次插入。

1.使用連接屬性啟用

新增 useBulkCopyForBatchInsert=true; 至 連接字串會啟用此功能。

Connection connection = DriverManager.getConnection("jdbc:sqlserver://<server>:<port>;userName=<user>;password=<password>;database=<database>;encrypt=true;useBulkCopyForBatchInsert=true;");

2.使用 SQLServerConnection 物件的 setUseBulkCopyForBatchInsert() 方法啟用

呼叫 SQLServerConnection.setUseBulkCopyForBatchInsert(true) 可啟用此功能。

SQLServerConnection.getUseBulkCopyForBatchInsert() 會擷取 useBulkCopyForBatchInsert 連接屬性的目前值。

useBulkCopyForBatchInsert 的值在其初始化時,會針對每個 PreparedStatement 保持不變。 對 SQLServerConnection.setUseBulkCopyForBatchInsert() 的任何後續呼叫,都不會影響已經建立的 PreparedStatement 值。

3.使用 SQLServerDataSource 物件的 setUseBulkCopyForBatchInsert() 方法啟用

與上述選項類似,但使用 SQLServerDataSource 來建立 SQLServerConnection 物件。 這兩種方法皆會獲得相同的結果。

已知限制

目前有適用於此功能的這些限制。

  • 不支援包含非參數化值的插入查詢 (例如,INSERT INTO TABLE VALUES (?, 2))。 萬用字元 (?) 是這個函式唯一支援的參數。
  • 不支援包含 INSERT-SELECT 運算式的插入查詢 (例如,INSERT INTO TABLE SELECT * FROM TABLE2)。
  • 不支援包含多個 VALUE 運算式的插入查詢 (例如,INSERT INTO TABLE VALUES (1, 2) (3, 4))。
  • 不支援後面接著 OPTION 子句、與多個資料表聯結,或後面接著另一個查詢的插入查詢。
  • 系統不是在驅動程式中管理 IDENTIY_INSERT。 在 insert 陳述式中不包含識別欄位、在批次 insert 陳述式之間手動設定資料表的 IDENTITY_INSERT 狀態,或使用 insert 陳述式手動傳遞識別欄位的明確值。 如需詳細資訊,請參閱 SET IDENTITY_INSERT
  • 由於大量複製 API 的限制,MONEYSMALLMONEYDATEDATETIMEDATETIMEOFFSETSMALLDATETIMETIMEGEOMETRYGEOGRAPHY 資料類型目前不支援此功能。

如果查詢因為非 SQL Server 執行個體相關錯誤而失敗,驅動程式將會記錄錯誤訊息,並遞補至批次插入的原始邏輯。

範例

以下範例針對一般與大量複製 API 這兩種情節,示範進行一千個資料列批次插入作業的使用案例。

    public static void main(String[] args) throws Exception
    {
        String tableName = "batchTest";
        String tableNameBulkCopyAPI = "batchTestBulk";

        String connectionUrl = "jdbc:sqlserver://<server>:<port>;encrypt=true;databaseName=<database>;user=<user>;password=<password>";

        try (Connection con = DriverManager.getConnection(connectionUrl);
                Statement stmt = con.createStatement();
                PreparedStatement pstmt = con.prepareStatement("insert into " + tableName + " values (?, ?)");) {

            String dropSql = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + tableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [" + tableName + "]";
            stmt.execute(dropSql);

            String createSql = "create table " + tableName + " (c1 int, c2 varchar(20))";
            stmt.execute(createSql);

            System.out.println("Starting batch operation using regular batch insert operation.");
            long start = System.currentTimeMillis();
            for (int i = 0; i < 1000; i++) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "test" + i);
                pstmt.addBatch();
            }
            pstmt.executeBatch();

            long end = System.currentTimeMillis();

            System.out.println("Finished. Time taken : " + (end - start) + " milliseconds.");
        }

        try (Connection con = DriverManager.getConnection(connectionUrl + ";useBulkCopyForBatchInsert=true");
                Statement stmt = con.createStatement();
                PreparedStatement pstmt = con.prepareStatement("insert into " + tableNameBulkCopyAPI + " values (?, ?)");) {

            String dropSql = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + tableNameBulkCopyAPI + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [" + tableNameBulkCopyAPI + "]";
            stmt.execute(dropSql);

            String createSql = "create table " + tableNameBulkCopyAPI + " (c1 int, c2 varchar(20))";
            stmt.execute(createSql);

            System.out.println("Starting batch operation using Bulk Copy API.");
            long start = System.currentTimeMillis();
            for (int i = 0; i < 1000; i++) {
                pstmt.setInt(1, i);
                pstmt.setString(2, "test" + i);
                pstmt.addBatch();
            }
            pstmt.executeBatch();

            long end = System.currentTimeMillis();

            System.out.println("Finished. Time taken : " + (end - start) + " milliseconds.");
        }
    }

結果:

Starting batch operation using regular batch insert operation.
Finished. Time taken : 104132 milliseconds.
Starting batch operation using Bulk Copy API.
Finished. Time taken : 1058 milliseconds.

另請參閱

改善JDBC 驅動程式的效能與可靠性