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

文章
03/08/2024

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 的限制，MONEY、SMALLMONEY、DATE、DATETIME、DATETIMEOFFSET、SMALLDATETIME、TIME、GEOMETRY 和 GEOGRAPHY 資料類型目前不支援此功能。

如果查詢因為非 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 驅動程式的效能與可靠性

分享方式：