useBulkCopyForBatchInsert : no ouput / slower

[JDBC-0]

Driver version

12.8.1

SQL Server version

Microsoft SQL Server 2019 (RTM-CU28-GDR) (KB5042749) - 15.0.4390.2 (X64) Aug 12 2024 13:08:42 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Client Operating System

Windows 10 Pro

JAVA/JVM version

21.0.4

Problem description

After we adjusted some tables from DATETIME to DATETIME2, changed all our prepared stored procedures to prepared INSERT statements, following all restrictions in "Known limitations" (https://learn.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver16#known-limitations) and switching URL parameter "useBulkCopyForBatchInsert" between true and false, we don't see any effect of this parameter!

Expected behavior

Batch INSERTs are faster with useBulkCopyForBatchInsert=true or info/warning message is logged if driver falls back to default batch INSERTs. As "Known Limitations" saiys:

"If the query fails because of errors unrelated to the SQL Server instance, the driver logs the error message and falls back to the original logic for batch insert."

Actual behavior

"errorCode=4022, message=Massenladedaten wurden erwartet, aber nicht gesendet. Der Batch wird beendet., SQLState=S0001, cause=null"
(4022: Bulk load data was expected but not sent. The batch will be terminated.)

Switching back to useBulkCopyForBatchInsert=false, no error occurs.

The problem interferes with #1784 (Bulk copy: fails with combination sendStringParametersAsUnicode=false). Also setting sendStringParametersAsUnicode=true , this error does not occur.

With
sendStringParametersAsUnicode=true;useBulkCopyForBatchInsert=true;sendTemporalDataTypesAsStringForBulkCopy=true
no error occurs, but the repeated test runs are SLOWER!

To avoid unexpected errors, we want to stay with sendStringParametersAsUnicode=false , but this causes the error above.

If we then set sendTemporalDataTypesAsStringForBulkCopy=false , the error occurs again.

We have no idea what other parameters in which combinations are required/necessary for bulk insert to work. We nocied that noo error occurs when alle 3 parameters

sendStringParametersAsUnicode=true;useBulkCopyForBatchInsert=true;sendTemporalDataTypesAsStringForBulkCopy=true

are either all "false" or either all "true". None of them show any performance improvements.

Any other details that can be helpful

JDBC URL: jdbc:sqlserver://{DBSERVERADR};databaseName=myDB;applicationName=myServer-{HOSTID};workstationID={HOSTID};integratedSecurity=true;sendStringParametersAsUnicode=false;encrypt=false;useBulkCopyForBatchInsert=true;sendTemporalDataTypesAsStringForBulkCopy=false

The mass INSERTs are done on connections with autocommit OFF and committing after each executeBatch().

[lilgreenbird]

this looks to be a duplicate of #1784 which is still under investigation. Please limit all correspondence to that issue to avoid any duplication.

[JDBC-0]

@lilgreenbird : it's not the same: #1784 is a special case with sendStringParametersAsUnicode .... this ticket here #2527 is a general ticket about bulk copy: how to detect what the driver is doing? We don't see any log output if the driver somehow decides not to use bulk copy feature.

It would be helpful to have at least a warning log entry why it is not used. And if it is used, an info log message would also be helpful to indicate that bulk copy feature is actually used. And if log messages are not feasable, at least some API to get the state of the bulk copy usage would be helpful. E.g. after the batch inserts, an API method offers the bulk copy state: either the state is "bulk copy was used" or "bulk copy wasn't used due to this reason: ....".