Sorry, I naively assumed the batched inserts would be done transparently using SQLServerBulkCopy with the connection string spec. For full disclosure, I only have a cursory knowledge of Java.
For maximum insert performance, you could use the
SQLServerBulkCopy class directly instead of traditional prepared statements. This will stream data such that performance is orders of magnitude faster than singleton or batched insert statements. I ran a quick test of this technique with the code below
and the latest jdbc driver and observed 4M rows in about 15 seconds.
I used a ResultSet for the source data in the example but one can also provide source data with other writeToServer overloads (SQLServerBulkCSVFileRecord or ISQLServerBulkData), depending on what is most convenient for the task at hand.
public static void sqlBulkCopyExample() throws SQLException, Exception {
String destinationTable = "dbo.TargetTable";
ResultSet sourceData;
try (Connection sourceConnection = DriverManager.getConnection(connectionString);
Connection destinationConnection = DriverManager.getConnection(connectionString);
Statement stmt = sourceConnection.createStatement();
SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(destinationConnection)) {
sourceData = stmt.executeQuery("WITH \r\n" +
" t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))\r\n" +
" ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)\r\n" +
" ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)\r\n" +
"SELECT num,num,num,num,num,num,num,num,num,num\r\n" +
"FROM t1g\r\n" +
"WHERE num <= 4000000;");
bulkCopy.setDestinationTableName(destinationTable);
bulkCopy.writeToServer(sourceData);
}
catch (SQLException e) {
e.printStackTrace();
}
}
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
