当从Java插入到SQL Server时，我可以获得类似于“批量插入”的速度吗？

Question

从Java插入时，插入速度约为每秒2500行。即使在我测量for循环之后和ExecuteBatch之前的时间时也是如此。因此“创建”内存中的数据并不是瓶颈。

使用批量插入进行插入时，插入速度约为每秒30000行。

这两个测试都是在服务器上完成的。所以网络也不是瓶颈。为什么批处理插入更快，有什么线索吗？如果在Java中也能获得同样的性能呢？

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

// This seems to be essential to get good speeds, otherwise batching is not used.
conn.setAutoCommit(false);

PreparedStatement prepStmt = conn.prepareStatement("INSERT INTO TestTable (Col1, Col2) VALUES (?, ?)");
for (int i = 1; i <= 10000; i++) {
    prepStmt.setString(1,"X");            
    prepStmt.setInt(2,100);
    prepStmt.addBatch();
}
prepStmt.executeBatch();
conn.commit();

// A text file containing "X 100" over and over again... so the same data as generated in JAVA
bulk insert TestTable FROM 'c:\test\test.txt';

Answer 1

虽然大容量插入是进行大容量插入的最快方式，但SQL Server支持通过本机驱动程序和ODBC进行远程（客户端驱动的）大容量插入操作。从JDBC驱动程序的4.2版开始，该功能通过SQLServerBulkCopy类公开，该类不直接从文件读取，但支持从Rowset、ResultSet或ISQLServerBulkRecord的自定义实现读取生成的数据。此功能等效于。NETSQLBulkCopy类，具有基本相同的接口，应该是执行大容量操作的最快方式，而不是基于服务器的大容量插入。

编辑：示例按OP

下面您可以找到一个示例用例，可以用来测试SQLServerBulkCSVFileRecord的性能，SQLServerBulkCopy是一个类似于SQLServerBulkCopy的方法，只是它从文本文件读取。在我的测试用例中，test.txt包含一百万行“xtab100”

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

// 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");