Como usar a API de cópia em massa para a operação de inserção em lote

  • Artigo

Baixar Driver JDBC

O Microsoft JDBC Driver para SQL Server versão 9.2 e posterior é compatível com o uso de uma API de Cópia em Massa para executar operações de inserção de lote. Esse recurso permite que os usuários habilitem o driver para executar operações de Cópia em Massa durante operações de inserção de lote. O driver visa obter um aprimoramento no desempenho ao inserir os mesmos dados que o driver teria com a operação de inserção em lote normal. O driver analisa a Consulta SQL do usuário usando uma API de Cópia em Massa em vez de uma operação normal de inserção de lote. As configurações a seguir são vários modos de habilitar uma API de Cópia em Massa para obter um recurso de inserção de lote, bem como listam as respectivas limitações. Essa página também contém um pequeno código de exemplo que demonstra um uso e o aumento de desempenho também.

Esse recurso só é aplicável a APIs executeBatch() e executeLargeBatch() de PreparedStatement e CallableStatement.

Pré-requisitos

Pré-requisitos para habilitar uma API de Cópia em Massa a fim de executar a inserção de lote.

  • A consulta deve ser de inserção (ela pode conter comentários, mas deve começar com a palavra-chave INSERT para que esse recurso entre em vigor).

Como habilitar a API de cópia em massa para inserção em lote

Há três maneiras de habilitar a API de Cópia em Massa para inserção em lote.

1. Como habilitar com a propriedade de conexão

Adicionar useBulkCopyForBatchInsert=true; à cadeia de conexão habilita esse recurso.

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

2. Como habilitar com o método setUseBulkCopyForBatchInsert() do objeto SQLServerConnection

Chamar SQLServerConnection.setUseBulkCopyForBatchInsert(true) habilita esse recurso.

SQLServerConnection.getUseBulkCopyForBatchInsert() recupera o valor atual da propriedade de conexão useBulkCopyForBatchInsert.

O valor de useBulkCopyForBatchInsert permanece constante para cada PreparedStatement no momento da inicialização. Chamadas subsequentes a SQLServerConnection.setUseBulkCopyForBatchInsert() não afetarão o valor de PreparedStatement criado anteriormente.

3. Como habilitar com o método setUseBulkCopyForBatchInsert() do objeto SQLServerDataSource

Semelhante à opção anterior, mas usando SQLServerDataSource para criar um objeto SQLServerConnection. Ambos os métodos geram o mesmo resultado.

Limitações conhecidas

No momento, há essas limitações aplicáveis a esse recurso.

  • Não há suporte para consultas de inserção que contenham valores não parametrizados (por exemplo, INSERT INTO TABLE VALUES (?, 2). Curingas (?) são os únicos parâmetros com suporte para essa função.
  • Não há suporte para consultas de inserção que contêm expressões INSERT-SELECT (por exemplo, INSERT INTO TABLE SELECT * FROM TABLE2).
  • Não há suporte para consultas de inserção que contenham múltiplas expressões VALUE (por exemplo, INSERT INTO TABLE VALUES (1, 2) (3, 4)).
  • Não há suporte para consultas de inserção que sejam seguidas pela cláusula OPTION, associadas a várias tabelas ou seguidas por outra consulta.
  • IDENTIY_INSERT não é gerenciado no driver. Não inclua colunas de identidade em instruções de inserção ou defina manualmente o estado IDENTITY_INSERT das tabelas entre instruções de inserção em lote ou passe manualmente o valor explícito para uma coluna de identidades com a instrução de inserção. Para obter mais informações, confira SET IDENTITY_INSERT.
  • Por causa das limitações de uma API de Cópia em Massa, os tipos de dados MONEY, SMALLMONEY, DATE, DATETIME, DATETIMEOFFSET, SMALLDATETIME, TIME, GEOMETRY e GEOGRAPHY atualmente não são compatíveis com esse recurso.

Se a consulta falhar devido a erros não relacionados à Instância do SQL Server, o driver registrará em log a mensagem de erro e fará fallback para a lógica original para inserção em lote.

Exemplo

Este exemplo demonstra o caso de uso de uma operação de inserção de lote de mil linhas para cenários comuns em comparação com os de uma API de Cópia em Massa.

    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.");
        }
    }

Resultado:

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.

Confira também

Melhorando o desempenho e a confiabilidade com o JDBC Driver