13.2: useBulkCopyForBatchInsert=true is much slower for partitioned tables

[JDBC-0]

Driver version

13.1.1 pre-release

SQL Server version

Microsoft SQL Server 2019 (RTM-CU32-GDR) (KB5058722) - 15.0.4435.7 (X64) Jun 9 2025 18:36:12 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Client Operating System

Windows 11 pro (24H2, Microsoft Windows [Version 10.0.26100.4652])

JAVA/JVM version

openjdk version "21.0.5" 2024-10-15 LTS
OpenJDK Runtime Environment Temurin-21.0.5+11 (build 21.0.5+11-LTS)
OpenJDK 64-Bit Server VM Temurin-21.0.5+11 (build 21.0.5+11-LTS, mixed mode, sharing)

Problem description

Testing driver version 13.1.1 pre-release with bulk copy feature. Compared to 12.10.0 the driver is slower:

• about 10 % slower by just exchanging the driver files (jar, dll)
• about 50 % slower when enabling bulkCopy feature (;useBulkCopyForBatchInsert=true)
  (These are tests with our big application saving thousands ... to millions of records.)

Expected behavior

Especially bulk copy should increase the speed.

Actual behavior

Even without bulkCopy enabled, the new driver is slower.

Error message/stack trace

none

Any other details that can be helpful

See #2527 (comment)

JDBC params:
...integratedSecurity=true;encrypt=true;trustServerCertificate=true;sendStringParametersAsUnicode=false;useBulkCopyForBatchInsert=true;sendTemporalDataTypesAsStringForBulkCopy=false;cacheBulkCopyMetadata=true

[machavan]

Hi @JDBC-0 ,

For Bulk Copy API functionality, the change 13.1.1 preview release that includes support for DATETIME datatype, which was missing previously, that should not really cause a performance regression, but we do want to investigate this further.

Can you share a representative application which demonstrates this slowness?

[JDBC-0]

testPerformance.txt

Well, I reused the test that I wrote for the problem witgh "useFlexibleCallableStatements": see "testPerformance.txt".

With this artificial test, I can see a performance improvement with 13.1.1 and useBulkCopyForBatchInsert=true for the "INSERT batch prepared" test.

I wonder why the new driver with bulkFeature enabled in our real world application is slower....

=> "Duration INSERT batch prepared" ist faster with 13.1.1 and bulkcopy feature enabled ... in this artificial test - but not in our real world application. :-/

A problem is that we don't see if bulk copy was used or not. Perhaps our real world application has still column types that are not supported and therefore the driver quietly doesn't use bulk copy?

[JDBC-0]

... I did some tests with our application and for a very small object, the result is also better with 13.1.1 + bulkCopy (8 seconds compared to 11 seconds with 12.10.0).

But for our bigger tests (about 5000 times the size compared to the small test) I get considerable slower results than with 12.10.0: 15 minutes instead of 11 minutes ... that's a no-go to update the driver. And these bigger processes are the reason why we want to have the bulk copy feature and test this pre-release 13.1.1.

What's really strange: if we just use the newer driver (13.1.1) but without the bulk copy enabled (useBulkCopyForBatchInsert=false) - we get a better result: 11 minutes, just like with 12.10.0.

We set the batch size to maximum (= only 1 single batch with a lot of prepared INSERT statements) as we expect bulk copy to benefit from this - or the other way round: we expect that many smaller batches (of about 5000 statements) can hurt the bulk copy feature (?).

[JDBC-0]

I configured bulkCopy yesterday on a test system and got an error

SQLServerException: Massenladedaten wurden erwartet, aber nicht gesendet. Der Batch wird beendet.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:278)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1788)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:3099)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:2947)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7822)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4828)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:321)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:253)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2326)

("Bulk load data was expected, but was not sent.", errorCode=4022, SQLState=S0001, cause=null)

I found the problem: the table column datatype ist "DATETIME" but we use PreparedStatement.setDate() ... after we changed it to setTimestamp() the error vanished.

=> the driver bulkCopy feature should also support type conversions for these cases!

No we would have to go through all the application codes to find JDBC calls for dates that might have a destination table column datatype "DATETIME".

[muskan124947]

Hi @JDBC-0 , thanks for reporting

1. The slowness issue you observed in Pre-release 13.1 has been addressed in Addressed performance issue on Bulk Copy API with batch insert #2735. With this fix, you should see a significant improvement in performance.

2. For batch size, it's recommended to send more data in less (or single batch) no. of batches. This reduces trips to the server and speeds up the operation.

[JDBC-0]

Looking forward to 13.1.2 / 13.2.0 ... :-)

[JDBC-0]

I'm sorry, but I tested 13.2.0 and don't see any performance improvement compared to 13.1.1. The smaller tests are still faster than 12.10.0 but the big tests are slower: still 15 minutes with 13.2 and bulkCopy enabled, compared to 11 minutes with 12.10.0 and bulk copy not enabled. (Double checked runtime versions, run multiple times, reverted back to 12.10.0 ... => 13.x driver is considerably slower than 12.10 !)

Our small benchmark test (see #2732 (comment)) does not show the problem because it's too simple.

[machavan]

@JDBC-0 Can you share the specific test scenario that shows the slowness?

Please share the values of useBulkCopyForBatchInsert, sendStringParametersAsUnicode and all the data types of columns for the table on which your test are doing insert.