Some customers are reporting bad performance on running INSERTs, SELECT INTOs, replications into MS SQL server while using Microsoft JDBC Driver to connect (rather than the alternative jTDS driver).
We have identified that the root cause for the issue is an open (as of time of writing this article) bug in the Microsoft JDBC Driver. The bug is described here
https://github.com/microsoft/mssql-jdbc/issues/1196
A workaround to this problem exists: if you experience this problem, please add the following parameters to the JDBC properties of the MS SQL data source:
statementPoolingCacheSize=10;disableStatementPooling=false;enablePrepareOnFirstPreparedStatementCall=true;
We will monitor the situation on the Microsoft side and update the driver in Data Virtuality release when the bug gets fixed.
Please note that also Azure SQL Database is affected by this. For Azure Synapse we strongly recommend configuring bulk inserts over Azure Blob Storage for best performance, in this case the aforementioned issue is not relevant.
Comments
3 comments
Hi Nick, I believe it's now possible with Azure SQL Database to use Blob storage to stage and load data similar to Polybase with Azure Synapse/dedicated SQL Pools.
Hi Sjors,
thank you for your feedback and sorry for late reply -- summer time :) We have added this to our product management system and my colleagues will look at it. I have to say that writing large data into Azure SQL Database is not a use case we see often -- we see Azure Synapse being used for this much more often and also recommend it to customers who manage large data volumes. Still, a very valid use case and improvement suggestion, thank you!!
best regards,
Nick
Microsoft solved the underlying issue in their JDBC driver starting with version 11.2.0.
Data Virtuality Platform includes the updated driver starting from version 3.14. Customers running these or later versions do not need the described workaround.
The customer running earlier version of Data Virtuality Platform are advised to upgrade to latest version.
Please sign in to leave a comment.