bulk insert java_从JAVA插入SQL Server时,我可以获得“BULK INSERT”般的速度吗？

虽然BULK INSERT是进行批量插入的最快方法,但SQL Server通过本机驱动程序和ODBC支持远程(客户端驱动)批量插入操作.

From version 4.2 onwards of the JDBC driver,此功能通过

SQLServerBulkCopy类公开,该类不直接从文件读取,但支持从RowSet,ResultSet或ISQLServerBulkRecord的自定义实现读取生成的数据.此功能等同于.NET SqlBulkCopy类,具有大致相同的接口,并且应该是执行批量操作的最快方式,而不是基于服务器的BULK INSERT.

编辑：OP的例子

下面是一个可用于测试SQLServerBulkCSVVileRecord性能的示例用例,这是一种类似于SQLServerBulkCopy的方法,除了它从文本文件中读取.在我的测试用例中,test.txt包含一百万行“X tab”

100\”

CREATE TABLE TestTable (Col1 varchar(50), Col2 int);

该表不应启用任何索引.

在JAVA

// Make sure to use version 4.2, as SQLServerBulkCSVFileRecord is not included in version 4.1

import com.microsoft.sqlserver.jdbc.*;

long startTime = System.currentTimeMillis();

SQLServerBulkCSVFileRecord fileRecord = null;

fileRecord = new SQLServerBulkCSVFileRecord("C:\\temp\\test.txt", true);

fileRecord.addColumnMetadata(1, null, java.sql.Types.NVARCHAR, 50, 0);

fileRecord.addColumnMetadata(2, null, java.sql.Types.INTEGER, 0, 0);

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Connection destinationConnection = DriverManager.getConnection("jdbc:sqlserver://Server\\\\Instance:1433", "user", "pass");

SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();

// Depending on the size of the data being uploaded, and the amount of RAM, an optimum can be found here. Play around with this to improve performance.

copyOptions.setBatchSize(300000);

// This is crucial to get good performance

copyOptions.setTableLock(true);

SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(destinationConnection);

bulkCopy.setBulkCopyOptions(copyOptions);

bulkCopy.setDestinationTableName("TestTable");

bulkCopy.writeToServer(fileRecord);

long endTime = System.currentTimeMillis();

long totalTime = endTime - startTime;

System.out.println(totalTime + "ms");

使用这个例子,我能够获得高达每秒30000行的插入速度.