java - 从 Java 插入到 SQL Server 时，我可以获得类似 "BULK INSERT"的速度吗？

Question

在寻找从 Java 获取数据到 SQL Server 的最快方法的过程中，我注意到我能想到的最快的 Java 方法仍然比使用 BULK INSERT 慢 12 倍。

我的数据是从 Java 中生成的，而 BULK INSERT 只支持从文本文件中读取数据，因此除非我将数据输出到临时文本文件，否则不能使用 BULK INSERT。反过来，这当然会对性能造成巨大影响。

从 Java 插入时，插入速度约为每秒 2500 行。 甚至 当我在 for 循环之后 和 executeBatch 之前测量时间时。因此，在内存中“创建”数据不是瓶颈。

使用 BATCH INSERT 插入时，插入速度约为每秒 30000 行。

这两个测试都是在服务器上完成的。所以网络也不是瓶颈。关于为什么 BATCH INSERT 更快的任何线索？而且，是否可以从 Java 中获得相同的性能？

这只是一个需要加载一次的大数据集。因此，可以暂时禁用任何类型的日志记录(已经尝试过简单的日志记录)、禁用索引(表没有)、锁定等等......

到目前为止我的测试设置

数据库:

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

Java:

// 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

虽然 BULK INSERT 是执行批量插入的最快方法，但 SQL Server 通过 native 驱动程序和 ODBC 支持远程(客户端驱动的)批量插入操作。 From version 4.2 onwards of the JDBC driver , 此功能通过 SQLServerBulkCopy 公开类，它不直接从文件中读取，但支持从 RowSet、ResultSet 或 ISQLServerBulkRecord 的自定义实现中读取生成的数据。此功能等效于 .NET SqlBulkCopy 类，具有大致相同的接口(interface)，并且应该是执行批量操作的最快方式，而不是基于服务器的 BULK INSERT。

编辑:OP 示例

您可以在下面找到一个示例用例，该用例可用于测试 SQLServerBulkCSVFileRecord 的性能，该方法类似于 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 行的插入速度。