Usando a API de cópia em massa para a operação de inserção em lote
O Microsoft JDBC Driver para SQL Server versão 9.2 e superior oferece suporte ao uso da API de cópia em massa para operações de inserção em lote. Esse recurso permite que os usuários habilitem o driver para fazer operações de cópia em massa por baixo ao executar operações de inserção em lote. O driver tem como objetivo alcançar uma melhoria no desempenho ao inserir os mesmos dados que o driver teria com a operação regular de inserção em lote. O driver analisa a consulta SQL do usuário, usando a API de cópia em massa em vez da operação usual de inserção em lote. As configurações a seguir são várias maneiras de habilitar a API de cópia em massa para o recurso de inserção em lote e lista suas limitações. Esta 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 às APIs de executeBatch() & executeLargeBatch() do PreparedStatement e do CallableStatement.
Pré-requisitos
Pré-requisito para habilitar a API de cópia em massa para inserção em lote.
- A consulta deve ser uma consulta de inserção (a consulta pode conter comentários, mas a consulta deve começar com a palavra-chave INSERT para que esse recurso entre em vigor).
Habilitando 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. Ativando com 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. Ativando 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 para useBulkCopyForBatchInsert permanece constante para cada PreparedStatement no momento de sua inicialização. Chamadas subsequentes para SQLServerConnection.setUseBulkCopyForBatchInsert() não afetam o valor do PreparedStatement já criado.
3. Habilitando com o método setUseBulkCopyForBatchInsert() do objeto SQLServerDataSource
Semelhante à opção anterior, mas usando SQLServerDataSource para criar um objeto SQLServerConnection. Ambos os métodos alcançam o mesmo resultado.
Limitações conhecidas
Atualmente, existem essas limitações que se aplicam a esse recurso.
- Não há suporte para inserir consultas que contenham valores não parametrizados (por exemplo,
INSERT INTO TABLE VALUES (?, 2)). Caracteres curinga (?) são os únicos parâmetros suportados para esta função. - Não há suporte para inserir consultas que contenham expressões INSERT-SELECT (por exemplo,
INSERT INTO TABLE SELECT * FROM TABLE2). - Não há suporte para inserir consultas que contenham várias expressões VALUE (por exemplo,
INSERT INTO TABLE VALUES (1, 2) (3, 4)). - Inserir consultas que são seguidas pela cláusula OPTION, unidas a várias tabelas ou seguidas por outra consulta não é suportado.
-
IDENTITY_INSERTnão é gerenciado no driver. Não inclua colunas de identidade nas instruções de inserção, defina manualmente o estadoIDENTITY_INSERTdas suas tabelas entre as instruções de inserção em lote ou passe o valor explícito de uma coluna de identidade manualmente com a instrução de inserção. Para obter mais informações, consulte SET IDENTITY_INSERT. - Devido às limitações da API de cópia em massa,
MONEY,SMALLMONEY,DATE,DATETIME,DATETIMEOFFSET,SMALLDATETIME,TIME,GEOMETRYeGEOGRAPHYtipos de dados atualmente não são suportados para esse recurso.
Se a consulta falhar devido a erros não relacionados à instância do SQL Server, o driver registrará a mensagem de erro e retornará à lógica original para inserção em lote.
Exemplo
Este exemplo demonstra o caso de uso de uma operação de inserção em lote de mil linhas, para ambos os cenários regulares vs 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.