JDBC Batching with prepared statement SQLServer 2019

Question

Hello,
I've been writing an application for import / export of a database.
So far I've been getting decent performance except with microsoft database.
I suspect, batching is not quite implemented with how slow it's running.

            performBindingOnPreparedStatement(con, prepStatement, datatypes, currentLineData);
            prepStatement.addBatch();

            if (i % batchSize == 0) {
                int[] executedBatches = null;

                executedBatches = prepStatement.executeBatch();
                con.commit();

                if (LOG.isTraceEnabled()) {
                    LOG.trace(Arrays.toString(executedBatches));
                }

                importedRecords += (executedBatches != null) ? executedBatches.length : 0;
            }

It's pretty straight forward.
I suspected issues with the blob/clob handling but my table it's so slow on, does not contain clobs/blobs.

Now to my table. It has 54 columns with multiple (8) varchar(max) columns.
It's rather big but I don't fully use those columns anyway.
JDBC Driver is mssql-jdbc-7.4.1.jre8.jar. Microsoft Server is 2019.
It has enough resources and statements generally are quite fast on that instance.
It's not having heavy loads or anything since it's used only for development.

Now on oracle I was having time of about 8 Minutes for 4~ Million Entries.
Postgres took about 16 minutes.
With SQLServer I'm 5 minutes in and I don't even have 20 K Entries done. So that's the reason I suspect, there is no actual batching happening.
I've not tried using:

Statement stmt = con.createStatement()
stmt.addbatch()

I would love some kind of help since I'm not really any further after 5 hours of analyzing and trying things differently.

Best Regards

Answer 1

Incase you are wondering.
I don't exactly know why I have to set these parameters since in the documentation it says, they wouldn't be needed and the driver was already optimized enough not needing to use them.

params.put("statementPoolingCacheSize", "" + batchSize);
params.put("disableStatementPooling", "false");
params.put("serverPreparedStatementDiscardThreshold", "" + batchSize);

But setting these parameters for the jdbc connection, actually makes it almost as performant as postgres/oracle.
It goes from 200000 Inserts per 30 minutes to about 10 million in the same time.

Answer 2

Welcome to Microsoft Q&A Platform.
For SQL server question, I suggest to ask for help from SQL server forum.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver
The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn.
Thanks for your understanding and cooperating.

Answer 3

Thanks, I'll try. I was thinking about posting there first but it said something about being moved to this page.